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

**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 th**e 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 Locatio**n, 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.