The following code is made to concatenate cells with a delimiter. The Ref here can be a continuous range or a group of ranges put inside a bracket. The only limitation i'm getting is that if the group of cells contain range from two different sheets the UDF gives value error.
Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
Dim plc2remove As Long
plc2remove = Len(Separator)
For Each Cell In Ref
If Not Cell.Value = "" Then
Result = Result & Cell.Value & Separator
End If
Next Cell
If Result = "" Then
CONCATENATEMULTIPLE = "NO DATA TO SHOW"
Else
CONCATENATEMULTIPLE = Left(Result, Len(Result) - plc2remove)
End If
End Function
I think the solution is to identify .parent property of each cell when iterating through it but I cannot make it work.
Concatenation works when group of ranges are from single sheet like this
But doesn't work when range from another sheet is also included like this
You could do something like this:
Function myConcat(Sep As String, ParamArray Ref() As Variant) As String
Dim s, c, m As String
m = ""
For Each s In Ref
For Each c In s.Cells
m = m & c.Value
If Len(c.Value) > 0 Then m = m & Sep
Next
Next
If Len(m) = 0 Then
myConcat= "NO DATA TO SHOW"
Else
myConcat = Left(m, Len(m) - Len(Sep))
End If
End Function
It can be called with something like:
=myConcat(", ",Sheet1!A1,Sheet2!A2,Sheet1!A3,Sheet2!A1,A1:A2)
Note - the ranges are not gathered in brackets.