Function ConCatRange22(CellBlock As Range, Optional Delim As String = "") _
As String

'entered as =concatrange22(a1:a10,"|") desired delimiter between quotes
'or as =concatrange22((a1:a10,e1,f1,g1:g4)"|") for non-contiguous ranges.

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

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

End Function


Gord Dibben MS Excel MVP


On Thu, 3 Jun 2010 08:24:10 -0700, Leporello
wrote:

Does anyone have an elegant way of concatenating a series of cells? I think
the answer will be a user defined function which will be equivalent to
MULTICONCAT(start cell : end cell). I think it needs to be a function rather
than a macro, so that I can include it in formulae elsewhere in the workbook.
I can manage with the limitation of the contents of the cells of a single
row or a single column, but it would be nice to have something which was a
bit more versatile and could handle a two dimensional array or, best of all,
non-contiguous cells.