Rifat Hassan
While working with VBA in Excel, we often have to develop Macro (s) to concatenate multiple columns in a worksheet. Concatenating columns is widely used in almost all the sectors in our works, starting from summarizing the results of students to analyzing a complex business. In this article, I’ll show you how you can develop a Macro to concatenate multiple columns in a worksheet in Excel. Along with developing a Macro, I’ll also show you to develop a User-Defined function (UDF) and a UserForm.
Macro to Concatenate Multiple Columns in Excel VBA (Quick View)
Sub Concatenate_Columns()
Dim Rng As Range
Set Rng = Range("B4:D14")
Dim Column_Numbers() As Variant
Column_Numbers = Array(1, 2, 3)
Separator = ", "
Output_Cell = "F4"
For i = 1 To Rng.Rows.Count
   Output = ""
   For j = LBound(Column_Numbers) To UBound(Column_Numbers)
       If j <> UBound(Column_Numbers) Then
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
       Else
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
       End If
   Next j
   Range(Output_Cell).Cells(i, 1) = Output
Next i
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Macro to Concatenate Multiple Columns in Excel VBA (Step by Step Analysis)
Here we’ve got a data set with the names of some employees, their joining dates, and salaries of a company called Johnson Group.
Now, let’s try to develop a Macro to concatenate the columns of this data set.
⧪ Step 1: Declaring the Range Whose Columns will be Concatenated
First of all, we have to declare the Range whose columns will be concatenated. Here it’s the data set, range B4:D13 (Excluding the Headers).
Dim Rng As Range
Set Rng = Range("B4:D14")⧪ Step 2: Declaring the Numbers of the Columns That will be Concatenated
Next, we have to declare an array consisting of the numbers of the columns that will be concatenated. Here we’ll concatenate all the columns of the range B4:D13 (1, 2, and 3).
Dim Column_Numbers() As Variant
Column_Numbers = Array(1, 2, 3)⧪ Step 3: Inserting the Separator
Then you have to insert the separator. Here I’ve inserted a comma accompanied by a space as the separator.
Separator = ", "⧪ Step 4: Inserting the Location of the Output
Then we have to insert the location of the output (Only the first cell). Here I’ve put F4.
Output_Cell = "F4"⧪ Step 5: Concatenating the Columns
Finally, we have to iterate through a for-loop to concatenate the columns in the desired location.
For i = 1 To Rng.Rows.Count
   Output = ""
   For j = LBound(Column_Numbers) To UBound(Column_Numbers)
       If j <> UBound(Column_Numbers) Then
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
       Else
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
       End If
   Next j
   Range(Output_Cell).Cells(i, 1) = Output
Next iTherefore, the complete VBA code will be:
⧠VBA Code:
Sub Concatenate_Columns()
Dim Rng As Range
Set Rng = Range("B4:D14")
Dim Column_Numbers() As Variant
Column_Numbers = Array(1, 2, 3)
Separator = ", "
Output_Cell = "F4"
For i = 1 To Rng.Rows.Count
   Output = ""
   For j = LBound(Column_Numbers) To UBound(Column_Numbers)
       If j <> UBound(Column_Numbers) Then
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
       Else
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
       End If
   Next j
   Range(Output_Cell).Cells(i, 1) = Output
Next i
End Sub
⧠Output:
Run the code by clicking the Run Sub/UserForm button in the VBA toolbar.
It’ll concatenate the 3 columns of the data set in the range F4:F13.
Examples to Concatenate Multiple Columns in Excel VBA (Involving Macro, UDF, and UserForm)
We’ve learned to concatenate multiple columns with VBA in Excel. This time we’ll explore some examples involving concatenating multiple columns with VBA.
Example 1: Developing a Macro to Concatenate Multiple Columns in a Different Worksheet in Excel VBA
We’ve learned to develop a Macro to concatenate multiple columns already. This time we’ll develop a Macro to concatenate multiple columns in a different worksheet.
Let’s develop a Macro to concatenate columns 1, 2, and 3 of the data set B4:D14 of Sheet1 in the range B4:B14 of Sheet2.
The VBA code will be:
⧠VBA Code:
Sub Concatenate_Columns_in_Different_Worksheet()
Dim Rng As Range
Set Rng = Worksheets("Sheet1").Range("B4:D14")
Dim Column_Numbers() As Variant
Column_Numbers = Array(1, 2, 3)
Separator = ", "
Output_Worksheet = "Sheet2"
Output_Cell = "B4"
For i = 1 To Rng.Rows.Count
   Output = ""
   For j = LBound(Column_Numbers) To UBound(Column_Numbers)
       If j <> UBound(Column_Numbers) Then
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
       Else
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
       End If
   Next j
   Worksheets(Output_Worksheet).Range(Output_Cell).Cells(i, 1) = Output
Next i
End Sub
⧠Output:
Run this code by clicking the Run Sub/UserForm button (See the earlier section). You’ll get the 3 columns concatenated in the range B4:B14 of Sheet2.
Read More: How to Combine Multiple Cells Into One Cell Separated By Comma In Excel (Functions & VBA)
Example 2: Creating a User-Defined Function to Concatenate Multiple Columns in Excel VBA
We’ve learned to develop a Macro to concatenate multiple columns of a data set. This time we’ll create a User-Defined function to concatenate multiple columns of a data set in Excel.
The complete VBA code will be:
⧠VBA Code:
Function ConcatenateColumns(Rng As Range, Column_Numbers() As Variant, Separator As String)
Dim Output_Column() As Variant
ReDim Output_Column(Rng.Rows.Count - 1, 0)
For i = 1 To Rng.Rows.Count
   Output = ""
   For j = LBound(Column_Numbers) To UBound(Column_Numbers)
       If j <> UBound(Column_Numbers) Then
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
       Else
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
       Output_Column(i - 1, 0) = Output
       End If
   Next j
   Output_Column(i - 1, 0) = Output
Next i
ConcatenateColumns = Output_Column
End Function
⧠Output:
Select the column where you want to concatenate the range and enter this formula:
=ConcatenateColumns(B4:D14,{1,3},", ")Here we’ll concatenate columns 1 and 3 of the data set B4:D14 (Employee Name and Salary). You use your one.
Then press CTRL + SHIFT + ENTER (Array Formula).
It’ll concatenate the 2 columns in the selected column (Column F here).
Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)
Similar Readings:
- Concatenate Numbers in Excel (4 Quick Formulas)
- How to Combine Rows into One Cell in Excel
- Combine Date and Text in Excel (5 Ways)
- How to Merge Rows in Excel (2 Easy Methods)
- Concatenate with Space in Excel (3 Suitable Ways)
Example 3: Developing a UserForm to Concatenate Multiple Columns in Excel VBA
We’ve learned to develop a Macro and a User-Defined function to concatenate multiple columns. Finally, we’ll develop a UserForm to concatenate multiple columns to a desired location of the desired worksheet.
⧪ Step 1: Inserting the UserForm
Go to the Insert > UserForm option in the VBA toolbar to insert a new UserForm.
⧪ Step 2: Dragging Tools to the UserForm
A UserForm called UserForm1 will be opened, along with a Toolbox called Control.
Move your mouse over the Toolbox and drag 2 ListBoxes, 4 TextBoxes, 6 Labels and1 CommanButtons in the UserForm.
Change the displays of the Labels as shown in the figure.
Similarly, change the display of the CommandButton to OK.
⧪ Step 3: Writing Code for TextBox1
Double click on TextBox1. A Private Subprocedure called TextBox1_Change will open. Enter the following code there.
Private Sub TextBox1_Change()
On Error GoTo Task
Range(UserForm1.TextBox1.Text).Select
UserForm1.ListBox1.Clear
For i = 1 To Range(UserForm1.TextBox1.Text).Columns.Count
   UserForm1.ListBox1.AddItem Range(UserForm1.TextBox1.Text).Cells(1, i)
Next i
Exit Sub
Task:
   x = 5
End Sub
⧪ Step 4: Writing Code for TextBox3
Similarly, double click on TextBox3. Another Private Subprocedure called TextBox3_Change will open. Enter the following code there.
Private Sub TextBox3_Change()
On Error GoTo Task
Starting_Cell = UserForm1.TextBox3.Text
For i = 1 To Len(Starting_Cell)
   If Asc(Mid(Starting_Cell, i, 1)) >= 48 And Asc(Mid(Starting_Cell, i, 1)) <= 57 Then
       Col = Left(Starting_Cell, i - 1)
       Row = Right(Starting_Cell, Len(Starting_Cell) - i + 1)
       End_Range = Col + Right(Str(Int(Row) + Range(UserForm1.TextBox1.Text).Rows.Count - 1), Len(Str(Int(Row) + 10)) - 1)
       Set Rng = Range(Starting_Cell + ":" + End_Range)
       Rng.Select
       Exit For
   End If
Next i
Exit Sub
Task:
   x = 5
End Sub
⧪ Step 5: Writing Code for ListBox2
Then double click on ListBox2. When the Private Subprocedure called ListBox2_Click opens, enter this code there.
Private Sub ListBox2_Click()
For i = 1 To UserForm1.ListBox2.ListCount - 1
   If UserForm1.ListBox2.Selected(i) = True Then
       Worksheets(UserForm1.ListBox2.List(i)).Activate
   End If
Next i
End Sub
⧪ Step 6: Writing Code for CommanButton1
Also, double-click on CommandButton1. After the Private Subprocedure called CommandButton1_Change opens, insert the following code there.
Private Sub CommandButton1_Click()
Dim Rng As Range
Set Rng = Range(UserForm1.TextBox1.Text)
Dim Column_Numbers() As Variant
Count = 0
For i = 0 To UserForm1.ListBox1.ListCount - 1
   If UserForm1.ListBox1.Selected(i) = True Then
       ReDim Preserve Column_Numbers(Count)
       Column_Numbers(Count) = i + 1
       Count = Count + 1
   End If
Next i
Separator = UserForm1.TextBox2.Text
Output_Cell = UserForm1.TextBox3.Text
If UserForm1.ListBox2.Selected(0) = True Then
   Sheet_Name = ActiveSheet.Name
Else
   For i = 1 To UserForm1.ListBox2.ListCount - 1
       If UserForm1.ListBox2.Selected(i) = True Then
          Sheet_Name = UserForm1.ListBox2.List(i)
       End If
   Next i
End If
For i = 2 To Rng.Rows.Count
   Output = ""
   For j = LBound(Column_Numbers) To UBound(Column_Numbers)
       If j <> UBound(Column_Numbers) Then
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
       Else
           Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
       End If
   Next j
   Worksheets(Sheet_Name).Range(Output_Cell).Cells(i, 1) = Output
Next i
End Sub
⧪ Step 7: Writing Code for Running the UserForm
Now is the final step. Insert a new Module from the VBA toolbar and insert the following code.
Sub Run_UserForm()
UserForm1.Caption = "Convatenate Multiple Columns"
UserForm1.TextBox1.Text = Selection.Address
UsrForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.MultiSelect = fmMultiSelectMulti
UserForm1.ListBox1.Clear
For i = 1 To Selection.Columns.Count
   UserForm1.ListBox1.AddItem Selection.Cells(1, i)
Next i
UserForm1.ListBox2.ListStyle = fmListStyleOption
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.AddItem "Active Sheet" + " (" + ActiveSheet.Name + ")"
For i = 1 To Sheets.Count
   If Sheets(i).Name <> ActiveSheet.Name Then
       UserForm1.ListBox2.AddItem Sheets(i).Name
   End If
Next i
Load UserForm1
UserForm1.Show
End Sub
⧪ Step 8: Running the UserForm
Your UserForm is now ready to use. To run it, run the Macro called Run_UserForm.
The UserForm will load with all the options. Select the Columns to Concat, enter the Separator, select the worksheet name from Concatenated In, insert the Output Location, and finally, enter the name of the Output Header.
Click OK. You’ll get the desired output in the desired location.
Read More: How to Concatenate Columns in Excel (8 Simple Methods)
Conclusion
So these are a few examples in which you can use Excel VBA to concatenate multiple columns. Hope the examples made everything pretty clear for you. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
