Function RangeConcat(ByVal delim As String, ByVal dotrim As Boolean, ParamArray arr()) As String

Dim rng As Range, x As Variant, y As Variant

For Each x In arr
If TypeOf x Is Range Then
For Each rng In x.Cells
If LenB(rng.Value) > 0 Then
RangeConcat = RangeConcat & rng.Value & delim
End If
Next rng
ElseIf IsArray(x) Then
For Each y In x
If LenB(y) > 0 Then
RangeConcat = RangeConcat & IIf(IsArray(y), RangeConcat(delim, False, y), y) & delim
End If
Next y
Else
If LenB(x) > 0 Then
RangeConcat = RangeConcat & x & delim
End If
End If
Next x

'Strip trailing delimiter if requested
If Len(RangeConcat) = 0 Then
RangeConcat = ""
Else
If dotrim Then
RangeConcat = Left(RangeConcat, Len(RangeConcat) - Len(delim))
End If
End If

End Function

 

Advertisements