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

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


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.

Data Set to Develop Macro to Concatenate Multiple Columns in Excel VBA

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

Macro to Concatenate Multiple Columns in Excel VBA

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

Output of the Macro to Concatenate Multiple Columns in Excel VBA


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

VBA Code to Develop Macro to Concatenate Multiple Columns in Excel VBA

⧭ 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

VBA Code to Develop Macro to Concatenate Multiple Columns in Excel VBA

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

Entering Function to Concatenate Multiple Columns in Excel VBA

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:


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.

Inserting UserForm to Develop Macro to Concatenate Multiple Columns in Excel VBA

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

Dragging Tools to Develop Macro to Concatenate Multiple Columns in Excel VBA

⧪ 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

TextBox Code to Develop Macro to Concatenate Multiple Columns in Excel VBA

⧪ 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

ListBox Code to Develop Macro to Concatenate Multiple Columns in Excel VBA

⧪ 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

UserForm Code to Develop Macro to Concatenate Multiple Columns in Excel VBA

⧪ Step 8: Running the UserForm

Your UserForm is now ready to use. To run it, run the Macro called Run_UserForm.

Running Macro to Concatenate Multiple Columns in Excel VBA

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

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

Leave a reply

ExcelDemy
Logo