Macro Help: Concatenate Populated Cells in Column A

  • Thread starter Thread starter TJM
  • Start date Start date
T

TJM

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
 
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
 
If the number of rows is always the same in column A, then you could use the
& funcionality in excel and simply us =A1&","&A2&","&a3...... however it will
not help if the result is variable in length.
 
Jason,

What code is to be used for not concatenating all contigiously used cells in
column A, but for all cells in a selected range, even if one ore more cells
in that range are blank (empty)?

Jack Sons
The Netherlands

Jason Morin said:
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 said:
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?action=getinfo&userid=12746
View this thread:
http://www.excelforum.com/showthread.php?threadid=378139
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top