Written by Are you frustrated that you have to concatenate a range, and you have to do that using CONCATENATE formula by entering each cell and typing a comma after every cell? Well, here’s a solution to it. A procedure or a function whatever you like. If you use the procedure, it allows you to choose the input range, concatenate character, and the output range. If you use the function, then you can enter the optional concatenate character (by default it is a comma (,)), and the input range.
Here are both:
Procedure:
Dim sChar2bAdded As String, rngRng2bCated As Range, sOutput As String, rngTarget As Range, c As Range
On Error GoTo ConCatwChar_Error
‘You could use this line to return the concatenated string in this cell
‘Set rngTarget = ActiveCell
Set rngRng2bCated = Application.InputBox(prompt:=”Select the range you’d like to concatenate with a charcter”, _
Title:=”Select Range”, Type:=8)
If rngRng2bCated Is Nothing Then Exit Sub
‘You could use this line to set the default to a comma and remove the inputbox line
‘sChar2bAdded = “,”
sChar2bAdded = InputBox(“Enter the character you’d like to add between other cells”, “Enter Character”, “,”)
Set rngTarget = Application.InputBox(prompt:=”Select the range you’d like the output”, _
Title:=”Select Range”, Type:=8)
For Each c In rngRng2bCated
sOutput = sOutput & c.Value & sChar2bAdded
Next c
sOutput = Left(sOutput, Len(sOutput) – Len(sChar2bAdded))
rngTarget = sOutput
On Error GoTo 0
Exit Sub
ConCatwChar_Error:
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure ConCatwChar”
End Sub
Here’s the function:
Public Function ConCatFunc(rngRng2bCated As Range, Optional sChar2bAdded As String = “,”) As String
Dim sOutput As String, c As Range
On Error GoTo ConCatFunc_Error
For Each c In rngRng2bCated
sOutput = sOutput & c.Value & sChar2bAdded
Next c
sOutput = Left(sOutput, Len(sOutput) – Len(sChar2bAdded))
ConCatFunc = sOutput
On Error GoTo 0
Exit Function
ConCatFunc_Error:
ConCatFunc = “#Error#”
End Function
Recent Comments