VBA Concatenate

Home ➜ VBA Tutorial ➜ VBA Concatenate

To concatenate two string using a VBA code, you need to use the ampersand. You can use an ampersand in between two strings to combine them and then assign that new value to a cell, variable, or a message box. In the same way, you can concatenate more than two values as well.

Further, we will see a simple example to understand it.

Steps to use VBA to Concatenate

1. First, enter the first string using the double quotation marks.
2. After that, type an ampersand.
3. Next, enter the second text using the double quotation marks.
4. In the end, assign that value to a cell, variable, or use a message box to see it.

Sub vba_concatenate()
Range("A1") = "Puneet " & "Gogia"
End Sub

Concatenate with a Delimiter

You can also use a delimiter within two strings by simply adding a third ampersand. Consider the following code.

Range("A1") = "Puneet " & "-" & "Gogia"

In the above code, you have used a delimiter within two strings and joined them by simply using ampersands. So basically, whenever you need to join anything you have to use an ampersand within.

Concatenate using Variables

You can also store values in variables and then concatenate value from those two variables. Consider the following code.

In the above code, you have variables that are declared as variables and then you have assigned values to those variables. And in the end, we have used ampersand to combine all the three variables and then assigned the result to the cell A1.

Concatenate a Range using VBA

You can also concatenate value from a range of cells using a VBA. Consider the following macro.

Sub vba_concatenate()

Dim rng As Range
Dim i As String
Dim SourceRange As Range

Set SourceRange = Range("A1:A10")

For Each rng In SourceRange
i = i & rng & " "
Next rng
Range("B1").Value = Trim(i)

End Sub

In the above code, you have used the FOR NEXT (For Loops) to loop through the range that you want to concatenate.

So it goes to each cell of the range (A1:A10) stores that value in i variable, and use an ampersand to concatenate a value with each iteration. In the end, set combine string to the range B1.

And the following code concatenates the values from the selected range. All you need to do is to select a range and then run the code.

Dim rng As Range
Dim i As String

For Each rng In Selection
i = i & rng & " "
Next rng

Range("B1").Value = Trim(i)

Concatenate Entire Column or a Row

If you want to concatenate an entire column or a row, in that case, it’s better not to use the loop method. You can use the worksheet function “TextJoin” that can join an entire row or a column (consider the following code).

'join values from column A.
Dim myRange As Range
Dim myString As String
Range("B1") = WorksheetFunction.TextJoin(" ", True, Range("A:A"))

'join values from row 1.
Dim myRange As Range
Dim myString As String
Range("B1") = WorksheetFunction.TextJoin(" ", True, Range("1:1"))

There’s More

VBA With Statement | VBA Wait and Sleep Commands | VBA Status Bar | VBA ScreenUpdating | VBA Random Number | Line Break in a VBA Code | VBA Immediate Window (Debug.Print) | VBA Module | VBA Random Number