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
- First, enter the first string using the double quotation marks.
- After that, type an ampersand.
- Next, enter the second text using the double quotation marks.
- 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"))