VBA to Concatenate Range with Separator in Excel (3 Ways)

In this article, I’ll show you how you can concatenate a range of cells with a given separator using VBA in Excel. You’ll learn to concatenate the rows within a range, the columns within a range, and finally, both the rows and columns within a range.


VBA to Concatenate Range with Separator in Excel (Quick View)

Sub Concatenate_Rows_and_Columns()

Separator = InputBox("Enter the Separator: ")

Location = InputBox("Enter the Cell Reference where You Want to Concatenate the Range: ")

Output = ""

For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        If VarType(Selection.Cells(j, i)) = 8 Then
            Output = Output + Selection.Cells(i, j) + Separator
       Else
            Output = Output + Str(Selection.Cells(i, j)) + Separator
        End If
    Next j
Next i

Range(Location) = Mid(Output, 1, Len(Output) - 1)

End Sub

VBA Code to Concatenate Range with Separator with VBA in Excel

Explanation of the Code:

  • This code creates a Macro called Concatenate_Rows_and_Coilumns.
  • It concatenates all the values from a selected range of cells into a single cell.
  • It takes two inputs given by the user. One is the separator by which the values will be separated, and the other is the cell location where the values will be concatenated.
  • The code uses the Addition sign (+) of VBA to concatenate two strings.
  • In case, a value within the range is not a string, the code first converts it into a string and then concatenates it.

Download Practice Workbook


3 Ways to Concatenate Range with Separator with VBA in Excel

Here we’ve got a data set with the names of some products and their sales of 5 years of a company called Mars Group.

Data Set to Concatenate Range with Separator with VBA in Excel

Today our objective is to concatenate values from this range with separator using Visual Basic Application (VBA) in Excel.


1. Concatenate Range According to Rows with Separator with VBA in Excel

First of all, we’ll try to concatenate a range that consists of only a row with VBA.

Let’s try to concatenate the sales of each product of all 5 years in single cells.

To develop the Macro, insert the following VBA code in a New Module.

VBA Code:

Sub Concatenate_Rows()

Separator = InputBox("Enter the Separator: ")

Output = ""

For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        If VarType(Selection.Cells(i, j)) = 8 Then
            Output = Output + Selection.Cells(i, j) + Separator
        Else
            Output = Output + Str(Selection.Cells(i, j)) + Separator
        End If
        Selection.Cells(i, Selection.Columns.Count + 1) = Mid(Output, 1, Len(Output) - 1)
    Next j
    Output = ""
Next i

End Sub

Note: This code creates a Macro called Concatenate_Row.

VBA Code to Concatenate Range with Separator with VBA in Excel

Output:

First, save the file as Excel Macro-Enabled Workbook.

Then select your data set and run the Macro (Delete_Rows).

Running Macro to Concatenate Range with Separator with VBA in Excel

You’ll get an Input box asking you to enter the separator.

Here, I’ve entered a comma (,) as the separator.

You enter anything you like.

Entering Input to Concatenate Range with Separator with VBA in Excel

Click OK. And you’ll get the sales record of each product concatenated in a column right to the selected range.

Read More: How to Concatenate String and Integer using VBA


2. Concatenate Range According to Columns with Separator with VBA in Excel

Next, we’ll try to concatenate the range according to columns with VBA.

Let’s try to concatenate the sales of each year of all the products in single cells.

Insert the following VBA Code in a new module to develop the Macro:

VBA Code:

Sub Concatenate_Columns()

Separator = InputBox("Enter the Separator: ")

Output = ""

For i = 1 To Selection.Columns.Count
    For j = 1 To Selection.Rows.Count
        If VarType(Selection.Cells(j, i)) = 8 Then
            Output = Output + Selection.Cells(j, i) + Separator
        Else
            Output = Output + Str(Selection.Cells(j, i)) + Separator
        End If
    Next j
    Selection.Cells(i, Selection.Columns.Count + 1) = Mid(Output, 1, Len(Output) - 1)
    Output = ""
Next i

End Sub

Note: This code creates a Macro called Concatenate_Columns.

VBA Code to Concatenate Range with Separator with VBA in Excel

Output:

Save the file as Excel Macro-Enabled Workbook.

Then select your data set and run the Macro (Delete_Columns).

Running Macro to Concatenate Range with Separator with VBA in Excel

You’ll get an Input Box asking you to enter the separator.

This time I’ve inserted a Hyphen (-) as the separator.

You use anything you like.

Then click OK.  You’ll find the sales of all the products in each year concatenated in a column right to your data set.

Output to Concatenate Range with Separator with VBA in Excel


Similar Readings:


3. Concatenate Range According to Both Rows and Columns with Separator with VBA in Excel

Finally, we’ll develop a Macro to concatenate all the cells of a range within a single cell with VBA.

Use the following VBA code for this purpose:

VBA Code:

Sub Concatenate_Columns()

Separator = InputBox("Enter the Separator: ")

Output = ""

For i = 1 To Selection.Columns.Count
    For j = 1 To Selection.Rows.Count
        If VarType(Selection.Cells(j, i)) = 8 Then
            Output = Output + Selection.Cells(j, i) + Separator
        Else
            Output = Output + Str(Selection.Cells(j, i)) + Separator
        End If
    Next j
    Selection.Cells(i, Selection.Columns.Count + 1) = Mid(Output, 1, Len(Output) - 1)
    Output = ""
Next i

End Sub

Note: This code creates a Macro called Concatenate_Rows_and_Columns.

VBA Code to Concatenate Range with Separator with VBA in Excel

Output: 

First, save the file as Excel Macro-Enabled Workbook.

Then select any range of cells from your worksheet and run the Macro (Delete_Rows_and_Columns).

Here I’ve selected range C4:E6.

Entering Input to Concatenate Range with Separator with VBA in Excel

You’ll get the same Input box asking you to enter the separator.

Here I’ve again entered a comma (,).

Click OK. This time, you’ll get another Input Box asking you to enter the reference of the cell where you want to concatenate the range.

I’ve inserted I4 in it.

Click OK. And you’ll get the selected range of cells merged in a single cell in your desired cell location (I4 in this example).

Concatenate Range in a Single Cell with Separator with VBA in Excel

Related Articles: How to Combine Rows into One Cell in Excel


Things to Remember

In all the codes of this article, we’ve used the Addition symbol (+) to concatenate two strings.

But you can also use the Ampersand symbol (&) to concatenate two strings in VBA.


Conclusion

Using these methods, you can concatenate a range of cells with VBA in Excel. You can concatenate the rows within a range, the columns within a range, and also, both the rows and columns within a range, according to your need. Do you have any questions? Feel free to ask us.


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