Macro Help: Concatenate Populated Cells in Column A

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

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

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.

"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
>
>

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" <[email protected]> schreef in bericht
news:[email protected]...
> 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
>>
>>