VBA Automatically concatenate several lines into one cell separated by a coma

[cogumelo]

Hi,

Hi have this list of emails on column A:

col A
line 1) email
line 2) email
line 3) email
line 4) email
etc.

i want to concatenate them all into one cell, so i can then copy it to my email client.

Something like this:

a1&","&a2&","&a3& etc.

However the last column is dynamic as it increases with more lines periodically. So i'd like to have a macro that does the concatenate by itself taking in account that the last line can be differente each time.

Is there a way to make this with VBA?


regards,

cogumelo

 

[Scott Huish]

Function combineEmails() As String
Application.Volatile
Dim c As Range, s As String
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    s = s & c & ","
Next
If Len(s) Then s = Left(s, Len(s) - 1)
combineEmails = s
End Function

Then in the cell you want:

=CombineEmails()

 

[My Aswer Is This]

Try This:</SPAN>
This script assumes your data is in column "A" and it places your result in Cell "E1"</SPAN>
Modify to your liking. The code highlighted in Red you can modify.</SPAN>

Sub addme()
Dim a
LastRow = Range("[B][COLOR=#b22222]A1[/COLOR][/B]").End(xlDown).Row
For a = 1 To LastRow
Range("[COLOR=#b22222][B]E1[/B][/COLOR]") = Range("[B][COLOR=#b22222]E1[/COLOR][/B]") & Cells(a, [B][COLOR=#b22222]1[/COLOR][/B]) & " "
Next
End Sub

 

[cogumelo]

Hi,

Thank you both for the excellent replies. i'm going with My Answer is This's solution which does this nicely.
regards