The Excel’s concatenate formula, or operator & do not work with ranges. You have to specify each parameter you want to add to the string. Sometimes it may get annoying, especially when the range is dynamic and depends on some parameters. Then it would be useful to have a formula that can work with ranges, and therefore also with indirect addressing.
The function below solves the problem
'' '' Concatenate values from every cell across the cell_range separated with separator '' Public Function ContactenateRange(ByVal cell_range As Range, _ Optional ByVal separator As String = "") As String Dim cell As Range, newString As String, cellArray As Variant Dim i As Long, j As Long cellArray = cell_range.Value 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 ContactenateRange = Mid$(newString, 2) Else ContactenateRange = newString End If End Function
The use is pretty straightforward. In Excel’s cell you just write =ConcatenateRage(A1:A5) and viola. In case you want to add a separator just add it as a second argument. The function works with character, numeric, dates, pretty much everything. You may find sample use in the screen below.