Try:

Sub MConcat()

Dim cell As Range
Dim rDestCell As Range
Dim nLastRow As Long
Dim strConcat As String
Const cDelim As String = ","

nLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rDestCell = ActiveSheet.[D1] '<-- Change cell

For Each cell In Range("A1:A" & nLastRow)
If Not IsEmpty(cell) Then
strConcat = strConcat & cell.Text & cDelim
End If
Next

rDestCell = Left(strConcat, Len(strConcat) - 1)

End Sub

---
HTH
Jason
Atlanta, GA

"TJM" wrote:


Hello all -

Here is what I am wanting to do: I have a spreadsheet that has X number
of cells populated in column A. I want to write a macro that will do the
following: concatenate all populated cells in column A into one cell,
separated by a comma (with no space).

Column A
A1
A2
A3

Becomes
A1,A2,A3

The cells in column A may vary depending on a given spreadsheet. I want
to be able to use the macro on any spreadsheet without having to tweak
the cell range manually.

Thanks!

Tom


--
TJM
------------------------------------------------------------------------
TJM's Profile: http://www.excelforum.com/member.php...o&userid=12746
View this thread: http://www.excelforum.com/showthread...hreadid=378139