How to Use Concatenate in Excel VBA (4 Methods)

 

Download the Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to the VBA Concatenate Function

⟴ Syntax

String1 = “First Text”

String2 = “Second Text”

⟴ Return Value

Return_value = String1 & String2


4 Different Uses of VBA Concatenate Function in Excel

Method 1 – Use the Ampersand (&) Operator to Join Cells in VBA Concatenate

We have a two-column dataset with first names in one column and last names in the other. By merging the two columns, we can get the full names.

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

Steps:

  • Press Alt + F11 to open the VBA code window.
  • Click Insert and select Module.

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

  • Copy and paste the following VBA code into the module.
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 Sub

Here,

  • 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 (&)

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

  • Save and press F5 to run the program.

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

  • Follow and repeat the steps for the other cells (changing the cell locations in the code).

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

Note. To run VBA code, every time make sure your Excel file is saved in Excel Macro-Enabled Worksheet (xlsm.) format.

Read More: How to Use VBA StrComp in Excel (5 Common Examples)


Method 2 – Use the Plus (+) Operator to Join Cells in VBA Concatenate

Let’s use the same dataset as before.

Use Plus (+) Operator to Join Cells in VBA Concatenate

Steps:

  • Press Alt + F11 to open the VBA window.
  • Click Insert and select Module.
  • Paste the following VBA code in the module:
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 Sub

Here,

  • Cells(5, 5).Value = String1 + String2 is this line we use plus (+) sign instead of the ampersand (&)

Use Plus (+) Operator to Join Cells in VBA Concatenate

  • Save and press F5 to run the program.

Use Plus (+) Operator to Join Cells in VBA Concatenate

  • Repeat the process by changing the cell positions inside the code (i.e., changing the row number).

Use Plus (+) Operator to Join Cells in VBA Concatenate

Read More: How to Use VBA StrConv Function (5 Examples)


Similar Readings:


Method 3 – Add Multiple Columns Using VBA Concatenate

In the previous two approaches, we combined two cells. If we want to apply that to the entire column, adding one by one will take a long time. Let’s use column-wide code to streamline the process.

Add Multiple Columns Using VBA Concatenate

Steps:

  • Press Alt+ F11 to open the VBA window and Insert a new Module.
  • Paste in the following VBA code:
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 Sub

Here,

  • 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.

 Add Multiple Columns

  • Save and press F5 to run the program.

 Add Multiple Columns


Method 4 – Join Multiple Rows Using VBA Concatenate

Let’s add three cells into one this time.

Join Multiple RowsSteps:

  • Press Alt+ F11 to open the VBA window and Insert a new Module.
  • Paste in the following VBA code:
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 Sub

Here,

  • Set SourceRange = Range(“B5:D5”) is the source cell range.
  • Range(“B8”).Value = Trim(i) is the return cell number.

Join Multiple Rows

  • Save the program and press F5 to run.

Join Multiple Rows

Read More: How to Unhide Top Rows in Excel (7 Methods)


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo