Concatenate a Range with Separator (optional)

The following custom function concatentates the data in a range...

ALT. 1: Source: link

....................

Function AConcat(a As Variant, Optional Sep As String = "") As String

    'By Harlan Grove, March 2002

    Dim Y As Variant

    If TypeOf a Is Range Then

        For Each Y In a.Cells

            AConcat = AConcat & Y.Value & Sep

        Next Y

    ElseIf IsArray(a) Then

        For Each Y In a

            AConcat = AConcat & Y & Sep

        Next Y

    Else

        AConcat = AConcat & a & Sep

    End If

   

    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))

   

End Function

----------------------------------------------------------

ALT2: link

...........

Function ConcRange(Substrings As Range, Optional Delim As String = ",", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = True)
      
    Dim CLL As Range
    
    For Each CLL In Substrings.Cells
        If Not (SkipBlanks And Trim(CLL) = "") Then
            ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
        End If
    Next CLL
     
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
     
End Function