Concatenate Array

Yesterday I made a post about concatenating range with strings in Excel. But there is one problem, it does not work with array formulas. So you cannot put condition on one column and based on that concatenate values form another. For this you would need a function like the one below.

Public Function ContatenateArray(ByVal cellArray As Variant, _
        Optional ByVal separator As String = "") As String
    Dim newString As String
    Dim i As Long, j As Long
    For i = 1 To UBound(cellArray, 1)
        For j = 1 To UBound(cellArray, 2)
            If Len(cellArray(i, j)) <> 0 Then
                newString = newString & (separator & cellArray(i, j))
            End If
        Next j
    Next i
    If separator <> "" Then
        ContatenateArray = Mid$(newString, 2)
        ContatenateArray = newString
    End If
End Function

And a sample use.

VBA formula for concatenating arrays.

VBA formula for concatenating arrays.

Leave a Reply