column values to a cell with comma seperated:with good example

[Raju Boine.]

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

--
HTH

Bob Phillips

"Raju Boine." <[email protected]> wrote in message
news:[email protected]...
> 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.

[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?

--
HTH

Bob Phillips

"olasa" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> olasa
> ------------------------------------------------------------------------
> olasa's Profile:
http://www.excelforum.com/member.php...o&userid=17760
> View this thread: http://www.excelforum.com/showthread...hreadid=390488
>