excel vba join variant array code example
Example: excel vba join variant array
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