Thanks all for your help

Both these get me what i'm after

cheers



"Gord Dibben" wrote:

Just a head's up.

If user has blank cells in the range, your code will produce extra
de-limiters.

Try this revision.

Function ConCatRange(CellBlock As Range) As String
'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) - 1)
End Function


Gord Dibben MS Excel MVP

On Wed, 23 Sep 2009 09:10:02 -0700, Gary''s Student
wrote:

Function RowThem(r As Range) As String
RowThem = ""
For Each rr In r
If RowThem = "" Then
RowThem = rr.Value
Else
RowThem = RowThem & "€¢|" & rr.Value
End If
Next
End Function