column values to a cell with comma seperated

[Guest]

I have a column in which I got some values. Now I want to get those values in
to a cell with comma seperated.

Eg:
In column A
123
234
345
456

In Cell B2 I want 123, 234, 345, 456. Can some please help me.

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1").NumberFormat = "@"
For i = iLastRow To 2 Step -1
Range("B1").Value = "," & Cells(i, "A").Value & Range("B1").Value
Next i
Range("B1").Value = Range("A1").Value & Range("B1").Value
End Sub

 

[olasa]

There's probably a much better way but...
1. Copy the column and Paste Special>Transpose to get all values in a
row
2. Save As>Book1.csv (comma delimited)
3. Rename the file to Book1.txt
4. Open the file and Replace all ; to ,
5. Copy and Paste to B2
Done

HTH
Ola Sandström

 

[Bob Phillips]

With all those steps, in what way is it better?