Most of us have used the Concatenate() function in Excel. It is a neat function that saves you the trouble of keying in “&” between the range addresses. The only thing that would make this awesome would be the ability to concatenate an entire range. That would be way more useful than having to comma separate each cell that you want to concatenate.
Consequently, I wrote this function that will concatenate all the cells in a specified range. It also has the option to include a “separator” text and it ignores blank cells.
'===================================================================
'Function to Concatenate the values stored in every Cell of a Range
'Author : Ejaz Ahmed
'Date : 14 November 2013
'Website : http://strugglingtoexcel.wordpress.com/
'===================================================================
Function ConcRange(ByRef WhichRange As Range, _
Optional ByVal Seperator As String = vbNullString) As Variant
'Begin by setting the ConcRange to a blank string
ConcRange = vbNullString
'Declare a Range object to use in the For Each Loop
Dim rngCell As Range
'For Each loop concatenates all the cells in the range
For Each rngCell In WhichRange
If ConcRange = vbNullString Then
If Not rngCell.Value = vbNullString Then
ConcRange = CStr(rngCell.Value2)
End If
Else
If Not rngCell.Value = vbNullString Then
ConcRange = ConcRange & Seperator & CStr(rngCell.Value2)
End If
End If
Next rngCell
End Function
Below is screenshot of a spreadsheet illustrating the use of this Functions:
