Concatenate range of rows

[AnilPullagura]

Hey Pros,

I was just thinking if this forum can help me out in solving my issue:

I have around 2000 rows of data in the range A2:A2001. Each cell(A1) has only 12 characters(alphanumerice) such as 11241F021500. I was trying to Concatenate all these cells to a single cell, say "G1".

Also i want these values to be seperated by a comma ",". I would prefer a macro which i can run for the above range.

I searched forums and found only UDF's, but i want to have it in a macro.
Also, the range is subject to change, it might NOT always be 2000, could be less or more.

Any help on this is greatly appreciated.

EXCEL 2010

Thanks,
Anil Pullagura

 

[villy]

Try:

Sub Con()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("G2").ClearContents
For i = 2 To lr
    If IsEmpty(Range("G2")) Then
        Range("G2").Value = Range("A" & i)
    Else
        Range("G2").Value = Range("G2") & "," & Range("A" & i)
    End If
Next
End Sub

 

[AnilPullagura]

Thanks Villy, it worked like magic

 

[villy]

Thanks Villy, it worked like magic

Glory to God...
Thanks I could help.

 

[Peter_SSs]

Here is another way.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Con2()<br>    Range("G2").Value = Join(Application.Transpose(Range("A2", Range("A" & Rows.Count).End(xlUp)).Value), ",")<br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>