Where's the initial announcement <G>?

Public Function MultiCat(ByRef rRng As Range, _
Optional ByVal sDelim As String = "") As String
Dim rCell As Range
For Each rCell In rRng
If rCell.Value <> "" Then
MultiCat = MultiCat & sDelim & rCell.Text
End If
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function


--
HTH

Bob Phillips

"Steve D" <stevedebruin@hotmail.com> wrote in message
news:uYUEIuguFHA.1572@TK2MSFTNGP10.phx.gbl...
> I apologize if this is too lengthy. I am using a User defined formula to
> take a range of values in the workbook and concatenate them into one long
> string value . I also want them to be separated by commas.
>
> Example (real data is much larger):
> A1: 011800
> A2: 121801
> A3: 051810
> A4:
> A5:
>
> I would like to use a formula in B1 that would get this result: 011800,
> 121801, 051810
>
> I have been able to accomplish this to an extent with the following user
> defined formula:
> Public Function MultiCat( _
> ByRef rRng As Excel.Range, _
> Optional ByVal sDelim As String = "") _
> As String
> Dim rCell As Range
> For Each rCell In rRng
> MultiCat = MultiCat & sDelim & rCell.Text
> Next rCell
> MultiCat = Mid(MultiCat, Len(sDelim) + 1)
> End Function
>
> But I am getting this result: 011800, 121801, 051810, , ,
>
> I get a comma for each blank cell. How can I get around this?
>
> Steve
>
>