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