excel vba concatenate code example

Example 1: excel vba concatenate range

Public Function ConcatenateRange(pRange As Range, Optional pDelimiter As String = " ") As String
    ConcatenateRange = Join(WorksheetFunction.Transpose(pRange.Value), pDelimiter)
End Function

Sub TestMe()
    Dim r As Range
    Set r = ThisWorkbook.Worksheets(1).Range("A1:A10")
    MsgBox ConcatenateRange(r)
End Sub

Example 2: vba concatenate string

"1" & "2"    => "12"
"1" + "2"    => "12"
"1" + 2      => 3
1 + "2"      => 3
"a" + 2      => type mismatch
"1" & Null   => "1"
"1" + Null   => Null