# 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.

### 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: Excel VBA to Concatenate Cell ValuesÂ

### 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 and 1 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
Â Â Â  UserForm1.ListBox1.AddItem Range(UserForm1.TextBox1.Text).Cells(1, i)
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
Â Â Â  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

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.

## 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.

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF