Excel VBA Tips: Large concatenation OR concatenating multiple ranges
One of the useful worksheet functions in excel is being able to concatenate cells into a single cell. However, if you try to concatenate a large number of cells or a multiple ranges, it can become very problematic.
However, after searching today, I came across this code:
The MultiCat() function allows you to use a more compact syntax:
= MultiCat(A1:C1," ")
MultiCat concatenates the text from the cells, rather than their underlying values. If you have the number 1234 in a cell formatted as “00000”, MultiCat will return “01234” while CONCATENATE will return 1234.
Put this in a regular code module.
'*****************************************
'Purpose: Concatenate all cells in a range
'Inputs: rRng - range to be concatenated
' sDelimiter - optional delimiter
' to insert between cell Texts
'Returns: concatenated string
'*****************************************
Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelim & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function
Fantastic! Works perfectly.
Come check out the directory for the rest of my excel tips!
Submeg
IT
SUBMEG 