Thread: Convert range of cells to comma separated list

I need to take the range of cells from C2:C50 and extract each cell's contents and convert them into a list of values separated by commas. The new list can be copied to the clipboard or inserted into a new cell.

For example: C1 = 50, C2 = 23 ---------convert to "50,23"

I'm doing this, because I need to use these values as query criteria in Access 07. Usually I will just paste the values into a new table and join on the field(s) needed for criteria, but in this case I think the best way to run this query is to "hardcode" the values into the actual SQL statement.

Function ConcatRange(Rng As Range, Optional Separator As String)
Dim awf As WorksheetFunction: Set awf = WorksheetFunction
Dim r As Range

For Each r In Rng
    ConcatRange = ConcatRange & awf.Text(r.Value, "") & Separator
Next
ConcatRange = Left(ConcatRange, Len(ConcatRange) - Len(Separator))
End Function

Regards, TMS

maw230,

In Excel VBA you could do somehting like this:

Range("A1") = Join(Application.Transpose(Range("C1:C2")), ",")

Range("A2") = Join(Application.Transpose(Range("C1:C50")), ",")

You're welcome. Thanks for the rep.