Sunday, February 16, 2025
No menu items!
HomeData Analytics and VisualizationHow to Concatenate in VBA

How to Concatenate in VBA

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

Read MoreListenData

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments