Concatenating a variable range in VBA

[Woffy]

I have a macro which will return a list of text values in a column. The length of the list can vary according to the data to which the macro is applied. I want to concatenate the values, split by ", " (as the next step in the macro, rather than by hand).

I understand how to do this if I know the length of the list (similarly to this thread: http://www.mrexcel.com/forum/showthread.php?t=479) but I want to know if there is a way to concatenate all the cells in a range using a single command, rather than by refering to each cell separately (as I don't know how many cells there will be).

Thanks

 

[Kaps_mr2]

In vba you can have Myrange.rows.count - which will count the number of rows in a range.

Hence broadly speaking you have:-

dim my_range as range
dim no_of_rows as long
dim overall_string as string
dim i as long

set my_range = Sheets("Sheet1").range("A1:2000")
no_of_rows = my_range.rows.count

for i = 1 to no_of_rows
overall_string = overall_string & my_range(i,1)
next i

Note that this is for guidance only - you'll need to modify for your situation. Thanks

Regards

Kaps

 

[Woffy]

Great, I'll try that. Many thanks for your help.

EDIT: Works brilliantly, thank you. I have used

overall_string=overall_string & my_range(i,1) & ", "

for all but the last i in order to incorporate the comma.