Excel 2007 : Selected columns to comma separated cell

**mossman** · 12-20-2009, 10:16 AM

Hello,

I need a macro that takes the selected cells values in a column and makes a single cell with comma separated values

example

45
48
50
55
60

Should look like this:
45, 48, 50, 55, 60

If anyone can help me i would be very happy!

/m

**rwgrietveld** · 12-20-2009, 10:21 AM

Function MyConCat(MyRng As Range) As String

  For Each Ccell In MyRng
    MyConCat = MyConCat & ", " & CStr(Ccell)
  Next
  MyConCat = Mid(MyConCat, 3)

End Function

**jeffreybrown** · 12-20-2009, 10:23 AM

Or

Sub concat()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B1").Value = Join(Application.Transpose(Range("A1:A" & LR)), ", ")
End Sub

**mossman** · 12-20-2009, 10:35 AM

Thanks both of you for really quick answer.

Is there a way to get the result of the script in the top cell of the selected range?

EDIT: the vba script does not seem to work like intended. I select cells, but it generates values from other places in the sheet

**jrdnoland** · 12-20-2009, 10:42 AM

Somelike this would do it. Not sure about where the data may reside initially. That would help determine where to position the rows and columns.

Dim X as Integer
Dim EndRow As Integer
Dim SColumn As Integer
Dim FirstRow As Integer
Dim NewAnswer As String

FirstRow = 7
EndRow = 17
SColumn = 6

    For X = FirstRow To EndRow
        NewAnswer = NewAnswer & Cells(X, SColumn).Value & " ,"
    Next X
    NewAnswer = Trim(Left(NewAnswer, Len(NewAnswer) - 1))
    Cells(7, 7).Value = NewAnswer