Concatenation of cells, automatically please (Excel 2000, Win 2000)

Topic

New Reply

Hello All

I have a need to concatenate a whole load of cells (300 plus) into one string, the contents of each cell must be separated by a comma followed by a space. Is there any way I can do this with code?? I’ve got up to about the 40th cell and I’m fed up already!!!

Thanks in advance.

Ian

Reply | Quote

Replies

See post 529,227. You can use the custom worksheet function from that post in a formula, for example

=Concatenate_Range(A1:A345,", ")

Reply | Quote

Hans

Thanks for that, but I’m getting error messages, not concatenated strings….

The errors are: –

expected line number or lable or statement or end of statement if I use =concatenate_range(a1:a1,”, “) The = is highlighted.
expected list separator if I use concatenate_range(a1:a1,”, “) The : is highlighted.

I not got a clue why either of these are showing!!!!

Ian

Reply | Quote

It doesn’t make sense to concatenate the range A1:A1: it consists of only a single cell.

You are supposed to put the code in a module and to place the formula = Concatenate_Range(A1:A10,”, “) or similar in a cell in a worksheet.

If you wish, you can attach (a stripped down copy of) your workbook.

Reply | Quote

So, that’s why it doesn’t work from a command button or while being called as a macro then!!!!!!!!! I’ll go back to my box now.

Just followed the instructions and it worked perfectly, you are my hero yet again Hans.

Thanks

Ian

Reply | Quote

You can use the function in VBA code too, but in that case you must provide a range object as first argument, for example:

Sub MyMacro()
Range(“B1”) = Concatenate_Range(Range(“A1:A100”), “, “)
End Sub

Reply | Quote