Concatenate non-blank values in the same column and add commas

[chris.huang]

Hi,

I would like to concatenate all rows in the same column into one string, separated by comma and ignore all the blank cells. The code I have below will concatenate values across columns, but I actually want to concatenate values in the same column. Thanks in advance!

Chris

Desired:
A........B.......C
1........3........4
2........5........6
1,2.....3,4.....4,6

Code execution give me this:
A........B.......C
1........3........4 --> 1,3,4
2........5........6 --> 2,5,6


------------------------------------------------------------

Please Login or Register  to view this content.

[JBeaucaire]

Instead of a macro, let's add a new function to your workbook you can use in a cell like any formula.

Insert this UDF into an emply code module (Insert > Module)

Please Login or Register  to view this content.

Close the vbeditor, then use this new function like so:

=CONCATVALUES(A1:A2, ",")

Whatever range of cells is in the first parameter will be listed and delimited using the character in the second parameter. You can leave OUT the second parameter and it will use a comma as a default.