Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy a list and skipping blanks | Excel Worksheet Functions | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions |