Or this one which ignores blank cells if any are present in the range.

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(range)


Gord Dibben MS Excel MVP


On Tue, 28 Oct 2008 12:28:10 -0700, Gary''s Student
wrote:

Try the following UDF:

Function mergum(r As Range) As String
mergum = ""
oneshot = 1
For Each cell In r
If oneshot = 1 Then
mergum = cell.Value
oneshot = 0
Else
mergum = mergum & "," & cell.Value
End If
Next
End Function