"Gord Dibben" <gorddibbATshawDOTca@ wrote...
....
Alternative.............UDF

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

=ConCatRange(A1:A110)
....

Generality and flexibility are good. Also a little trickery goes a long way.

http://groups.google.com/group/micro...e=source&hl=en

(or http://makeashorterlink.com/?S1E33459B ) includes a function named mcat
that takes a variable number of arguments, like SUM. It could be used in

=SUBSTITUTE(TRIM(MCAT(" "&YourRangeAddressHere))," ",",")

As for trickery, much better in VBA to append the field separator between
sbuf and the next entry, so

sbuf = sbuf & "," & Cell.text

which puts an extraneous comma at the beginning of sbuf. It's easier and
more efficient then to use

ConCatRange = Mid$(sbuf, 2)