VBA Concatenate

To concatenate two strings 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 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 double quotation marks.
2. After that, type an ampersand.
3. Next, enter the second text using double quotation marks.
4. In the end, assign that value to a cell, or 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 values 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 used an ampersand to combine all three variables and then assigned the result to cell A1.

Concatenate a Range using VBA

You can also concatenate values 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 the I variable, and uses an ampersand to concatenate a value with each iteration. In the end, set the combined string to 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” which 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