How to Concatenate in VBA

Yash Gaur Add Comment

This tutorial explains how to concatenate strings in Excel using VBA code.

You can download the following dataset to practice.

We can concatenate (combine) strings using the ampersand & operator.
1. Concatenate Two Strings

In the following code, we have used & operator to combine strings - "My name" and "is Lucifer.".

Sub Concatenate2strings()
    Dim str1 As String
    Dim str2 As String
    Dim result As String
    
    str1 = "My name"
    str2 = "is Lucifer."
    
    result = str1 & " " & str2

    MsgBox result 
End Sub
Press Run or F5 to run the above macro.
VBA : Concatenate Strings
We use " " to add a space between two strings.
2. Concatenate Multiple Columns

The following code can be used to concatenate the values stored in columns A and column B and copy the final output to column C :

Sub ConcatenateRangeValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        
    
    For i = 2 To lastRow
       ws.Cells(i, 3).Value = ws.Cells(i, 1).Value & " " & ws.Cells(i, 2).Value
    Next i
End Sub
Press Run or F5 to run the above macro.
VBA : Concatenate Multiple Columns
Related Posts
Spread the Word!
Share
About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Post Comment 0 Response to "How to Concatenate in VBA"
Next → ← Prev