If Gord will excuse me playing with his code then you do it like this.
Delimeter is optional. you enter your own or if you leave it the default
comma is used

=concatrange(A1:A100,"-")

or

=concatrange(A1:A100)



Function ConCatRange(CellBlock As Range, _
Optional delimeter As String) As String
If delimeter = "" Then delimeter = ","
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & delimeter
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Mike

"JBeaucaire" wrote:

That is wonderfully concise. Is there any way to add an argument so the
delimiters (if any) are added in the user function?

=ConcatRange(", ",A1:A1000)

I would love to use the briefer version, but I need to retain the ability to
define the delimiter or use no delimiter at all.

Thanks.
--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Gord Dibben" wrote:

Add this UDF to a general module.

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:A1000)


Gord Dibben MS Excel MVP