user1316897
user1316897

Reputation: 21

Concatenating alternate columns

I have 10,000 rows and unlimited data in columns. Two parameters in adjacent columns and are repeating after 13 unwanted columns.

I want to concatenate these adjacent columns of a row separated by space

eg:

concatenate(B2,C2,"",P2,Q2,"",AC,AD,"",....)

But I dont know upto which column the data is present.

Can you suggest me a macro which concatenates data upto blank column in one cell of a row and same is continued for first 10,000 rows. Thanks a lot for help !

Upvotes: 2

Views: 307

Answers (1)

Gaffi
Gaffi

Reputation: 4367

First off, are all the rows terminating at the same column? i.e. does every row have data in column AD but not AE?

If the answer to the above is 'yes', then you'll probably want to take a look at:

Range.End()

which is used like:

YourSheet.Range(YourRange).End(xlToRight).Column

(see here for more info)

This will return either

  • the last column with data (when the starting cell contains data)

or

  • the first column with data (when the starting cell is empty).
    • not the last column without data!

Based on your example in the question, your ranges probably start in Column B, P, AC, etc.

If the answer to the above is 'no', then you can use similar functionality, but you'll have to loop through each row...

Upvotes: 2

Related Questions