Converting a column to a list
 

I have a column of email addresses with each row being a separate address. I
want to write a macro that will give me a mailing list of addresses separated
by commas. It sounds like an easy common task but I have had no luck. Can
somebody help?

Converting a column to a list
 

As requested in macro format

Sub main()
Dim myString As String

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
'change i = to first row of data and "A" to column data is in
If myString = "" Then
myString = Cells(i, 1) 'change one to data column
Else: myString = myString & ", " & Cells(i, 1)
End If

Next

Cells(2, 2) = myString 'where to output result

End Sub


--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Joel" wrote in message
...
I have a column of email addresses with each row being a separate address.
I
want to write a macro that will give me a mailing list of addresses
separated
by commas. It sounds like an easy common task but I have had no luck. Can
somebody help?

Converting a column to a list
 

Joel

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A10) or whatever your range is.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 09:31:00 -0800, Joel wrote:

I have a column of email addresses with each row being a separate address. I
want to write a macro that will give me a mailing list of addresses separated
by commas. It sounds like an easy common task but I have had no luck. Can
somebody help?

Converting a column to a list
 

Well, thanks ... but I need to know how to create a function. Can you give me
a tip or two?

Joel

"Gord Dibben" wrote:

Joel

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A10) or whatever your range is.


Gord Dibben MS Excel MVP

Converting a column to a list
 

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the ConCatRange code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as shown in "Usage is:"


Gord

On Tue, 16 Jan 2007 10:03:00 -0800, Joel wrote:

Well, thanks ... but I need to know how to create a function. Can you give me
a tip or two?

Joel

"Gord Dibben" wrote:

Joel

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A10) or whatever your range is.


Gord Dibben MS Excel MVP