Bhubon Costa
In Excel, concatenation is the process of joining two strings together to form a single string. Simply said, if we have a table with first names in one column and last names in another, we can use the concatenation procedure to concatenate and combine them in a single cell in a split second. In Excel, we have a function called CONCATENATE () that allows us to do this concatenation. However, in VBA, this type of function is not permitted. We can’t use CONCATENATE () in the VBA code since it won’t work. Because VBA lacks built-in functions and we can’t utilize spreadsheet functions. So, this lesson will demonstrate to you how to use VBA concatenate to combine multiple cells, columns, and rows in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to the VBA Concatenate Function
As we have mentioned that Excel has not any built-in function for the VBA Concatenate, but we can make it works as a function by combining different strings with operators. Here we use the ampersand (&) as our operator.
⟴ Syntax
String1 = “First Text”
String2 = “Second Text”
⟴ Return Value
Return_value = String1 & String2
4 Different Uses of VBA Concatenate Function in Excel
Here, we will use 4 different approaches to perform the concatenate process. We will apply different operators in combination with VBA code to achieve this.
1. Use Ampersand (&) Operator to Join Cells in VBA Concatenate
As shown in the below screenshot, we have a two-column data collection with first names in one column and last names in the other. By merging the two columns, we can now get the whole names. Because VBA doesn’t have any built-in methods for concatenation, we’ll use the ampersand (&) operator as detailed in the instructions below.
Step 1:
- First of all, press Alt + F11 to open the Macro-Enabled Worksheet.
- Then, click
- Select Module.
Step 2:
- To combine two cells into one, copy and paste the following VBA
Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = Cells(5, 2).Value
String2 = Cells(5, 3).Value
Cells(5, 5).Value = String1 & String2
MsgBox (full_string)
End SubHere,
- String1 = Cells(5, 2).Value is the first cell location B5, row 5, and column 2.
- String2 = Cells(5, 3).Value is the second cell location C5, row 5, and column 3.
- Cells(5, 5).Value = String1 & String2 is the result cell location E5, row 5 and column 5.
- String1 & String2 are the two strings joined by the ampersand (&)
Step 3:
- Save and press F5 to run the program.
Therefore, you will get the result in the E5 cell of your current worksheet.
Step 4:
- Follow and repeat the steps for the rest cells and get the results as shown in the below image.
Read More: How to Use VBA StrComp in Excel (5 Common Examples)
2. Use Plus (+) Operator to Join Cells in VBA Concatenate
As described in the previous section, we have used the ampersand (&) operator to join cells strings. You can obtain the same result by applying the plus (+) sign in lieu of the ampersand (&) operator. To have it done follow these steps.
Step 1:
- To open Macro in Excel, press Alt + F11.
- Click Insert and select
- After opening the program page, paste the following VBA
Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = Cells(5, 2).Value
String2 = Cells(5, 3).Value
Cells(5, 5).Value = String1 + String2
MsgBox (full_string)
End SubHere,
- Cells(5, 5).Value = String1 + String2 is this line we use plus (+) sign instead of the ampersand (&)
Step 2:
- After pasting, save and press F5 to run the program. Consequently, you will see the change in cell E5.
- To obtain the final results, fill up the required cells by performing the previous steps again.
Read More: How to Use VBA StrConv Function (5 Examples)
Similar Readings:
- How to Call a Sub in VBA in Excel (4 Examples)
- Return a Value in VBA Function (Both Array and Non-Array Values)
- How to Use VBA DIR Function in Excel (7 Examples)
- Use VBA UCASE Function in Excel (4 Examples)
- How to Use InStr Function in VBA (3 Examples)
3. Add Multiple Columns Using VBA Concatenate
In the previous two approaches, we discussed how to combine two cells. However, if we want to apply it to the entire column, adding one by one will take a long time. We’ll teach you how to add multiple columns totally with VBA code for this.
Step 1:
- Firstly, to open Macro press Alt + F11
- Choose Module from the Insert tab
- Then, paste the following VBA
Sub ConcatCols()
'concatenate columns B & C in column E
Dim LastRow As Long
With Worksheets("Sheet3")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("E5:E" & LastRow)
.Formula = "=B5&C5"
.Value = .Value
End With
End With
End SubHere,
- With Worksheets(“Sheet3”) is your current worksheet name.
- LastRow = .Cells(.Rows.Count, “B”).End(xlUp).Row is the first column name.
- With .Range(“E5:E” & LastRow) is the result return cell range.
- .Formula = “=B5&C5” is the formula to join the first cell of the range.
Step 2:
- Then, finally, save and press F5 to run the program.
As a result, you will obtain the results entirely in a column.
Read More: How to Use VBA Rnd in Excel (4 Methods)
4. Join Multiple Rows Using VBA Concatenate
In addition to adding multiple columns, we can also apply VBA code to concatenate multiple rows into one. As shown in the below screenshot, we want to concatenate the three rows into one. To concatenate the rows, follow the simple steps below.
Step 1:
- For activating Macro in Excel, press Alt + F11.
- Then, select Module from the Insert
- To concatenate the rows, paste the VBA
Sub vba_concatenate()
Dim rng As Range
Dim i As String
Dim SourceRange As Range
Set SourceRange = Range("B5:D5")
For Each rng In SourceRange
i = i & rng & " "
Next rng
Range("B8").Value = Trim(i)
End SubHere,
- Set SourceRange = Range(“B5:D5”) is the source cell range.
- Range(“B8”).Value = Trim(i) is the return cell number.
Step 2:
- Finally, save the program and press F5 to run.
Thus, the final result concatenating the three rows will be shown in cell B8.
Read More: How to Unhide Top Rows in Excel (7 Methods)
Conclusion
To summarize, I hope that this post has provided clear instructions on how to utilize VBA concatenate in Excel in a variety of ways. All of these techniques should be learned and used on your data. Examine the practice book and put your newfound knowledge to use. Because of your kind support, we’re motivated to continue creating workshops like this.
Please do not hesitate to contact us if you have any queries. Please let us know what you think in the comments area below.
The Exceldemy Team will constantly respond to your questions.
