# Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)

Get FREE Advanced Excel Exercises with Solutions!

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`````` ## 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 i``````

Therefore, 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)

### 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()

Range(UserForm1.TextBox1.Text).Select

UserForm1.ListBox1.Clear

For i = 1 To Range(UserForm1.TextBox1.Text).Columns.Count
Next i

Exit Sub

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()

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

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"
UsrForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.MultiSelect = fmMultiSelectMulti
UserForm1.ListBox1.Clear

For i = 1 To Selection.Columns.Count
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
End If
Next i

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.

## Related Articles #### Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  