Here is a slightly shorter, loopless UDF (if that is the way the OP wants to
go)...

Function ConcatCellsInColumn(ColumnRange As Range) As String
ConcatCellsInColumn = Join(WorksheetFunction.Transpose(ColumnRange), ",")
End Function

Note: This function will only work for data in a single column.

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
If not too many try

=A1 & "," & A2 & "," etc.

Or use a UDF to give you comma-separated list in one cell.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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) - 2)
End Function

Copy/paste to a General module in your workbook.

usage is =ConCatRange(A1:A50)


Gord Dibben MS Excel MVP

On Wed, 18 Nov 2009 11:41:02 -0800, Renegade
wrote:

I am working in Excel XP, is there a way to take a column of email
addresses
and concatenate them into one cell to create a distribution list for email
purposes? Any assistance would be greatly appreciated.

Thanks.

Renegade