Concatenate range

Brief description:

Concatenate all cells in range with an optional separator

Full description:

Returns a concatenated string of all cells in range with an optional argument to define a separation character.

Note: Excel has recently introduced a native function that accomplishes this task. However, it does not include the option of a separator so this function is not yet completely obsolete.

To use: Call the function from within Excel using the range selection tool to select the range to be concatenated.

Add a defined separating string if required:

Future feature to add: An option to skip blank cells and an option to move between column and row concatenation.

Code:

Function ConcatenateRange(SelectionRange As Range, Optional InsertStr As String)
    'Concatenates a range of values
    '  Optional 2nd argument can be used to insert characters between each value ie comma, space etc
    
    Dim CurrCell As Range
    Dim TmpStr As String
    Dim CountCell As Integer
    
    TmpStr = ""
    CountCell = 0
    For Each CurrCell In SelectionRange
        CountCell = CountCell + 1
        If CountCell < SelectionRange.Count Then
            If CurrCell <> "" Then TmpStr = TmpStr & CurrCell & InsertStr
        Else
            If CurrCell <> "" Then TmpStr = TmpStr & CurrCell
        End If
        
    Next CurrCell
    

  ConcatenateRange = TmpStr

End Function