Function ConCatRange(CellBlock As Range) As String

'=concatrange(range)
'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

Ignores blanks and can be used for non-contiguous ranges.


Gord Dibben MS Excel MVP

On Thu, 3 Jun 2010 10:17:08 -0700, MaggieB.
wrote:

I have a list of 600+ zip codes that are curently in individual cells. I need
to turn them into one list separated by commas. Is this possible? The best
solution I can think of is
=concatenate(A1, ", ",B1)

However, is there a way to automate this instead of individually typing this
in for all 600+ cells?

Thanks!