I like the improvement Dave.

Gord

On Tue, 13 Jan 2009 14:42:04 -0600, Dave Peterson
wrote:

Option Explicit
Function ConCatRange(CellBlock As Range, Optional Delim As String = "") _
As String

Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.Text < "" Then
sbuf = sbuf & Cell.Text & Delim
End If
Next Cell

ConCatRange = Left(sbuf, Len(sbuf) - Len(Delim))

End Function

(With minor variations to Gord's code--just to be different!)

=ConcatRange(A1:A1000, ", ")
or
=ConcatRange(A1:A1000, "")
or even
=ConcatRange(A1:A1000)


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