How to Combine Multiple Columns in Excel Using VBA

Why Use VBA Instead of Excel Formulas?
Step-by-Step Guide: Combine Multiple Columns Using VBA
Pro Tips for Combining Columns Efficiently
Alternative Methods: VBA vs Formulas vs Power Query
Download the Excel Practice Sheet (Hands-on Learning!)
Combine Multiple Columns FAQs

Concatenating multiple columns in Excel manually can be quite a tedious task, especially when dealing with large datasets. But don’t worry! There’s a better way to do it.

Did You Know? According to Microsoft, over 750 million people use Excel worldwide, and businesses waste 30% of their time on manual Excel tasks. Why spend hours merging columns manually when a VBA macro can do it in seconds?

Rather than relying on manual work or formulas, you can easily automate this process using a VBA macro. With just a few clicks, you can quickly combine multiple columns into one without losing any data.

In this guide, I will walk you through step by step on how to combine multiple columns into a single column using a macro in Excel. Not only that, but you will also learn how to merge more than two columns while using separators like spaces, commas, and semicolons. On top of that, I’ll show you how to remove duplicates effortlessly while merging cells in Excel.

Why Use VBA Instead of Excel Formulas?

Method	Best For	Automation	Limitations
CONCATENATE / &	Small datasets	No	Cannot handle blank cells well
TEXTJOIN (Excel 2016+)	Dynamic merging	No	Requires delimiter
VBA Macro	Large datasets	Yes	Requires enabling macros

Pro Tip: If you need a one-time combination, use TEXTJOIN. If you need to merge columns frequently, use VBA.

For merging only two columns efficiently, check out How to Combine Two Columns in Excel.

Step-by-Step Guide: Combine Multiple Columns Using VBA

Step 1: Open the VBA Editor

You need to press Alt + F11 to open the VBA Editor.
Then click on Insert > Module.

Step 2: Paste This VBA Macro to Combine Columns

Sub CombineColumns()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim combinedText As String
    
    ' Set active worksheet
    Set ws = ActiveSheet
    
    ' Find the last used row and column dynamically
    lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    ' Loop through each row to combine columns
    For i = 1 To lastRow
        combinedText = "" ' Reset string for each row
        For j = 1 To lastCol
            ' Add cell value if it's not empty
            If ws.Cells(i, j).Value <> "" Then
                combinedText = combinedText & ws.Cells(i, j).Value & " "
            End If
        Next j
        ' Trim and store in next column
        ws.Cells(i, lastCol + 1).Value = Trim(combinedText)
    Next i
    
    MsgBox "Columns combined successfully!", vbInformation, "Done"
End Sub

Step 3: Run the Macro

You can place your data anywhere on the sheet.

Now, you can run the macro (Shortcut: Alt + F8) or Developers > Macros > Run.

You will see combined values in the next available column.

Example Input:

Column A	Column B	Column C
John	Doe	12345
Alice	Smith	67890

Output (Next Available Column):

Column A	Column B	Column C	Column D
John	Doe	12345	John Doe 12345
Alice	Smith	67890	Alice Smith 67890

Output result in an image:

Combine multiple cells in excel using VBA

Customize the Macro

1. Add a Custom Separator (Comma, Space, Semicolon, etc.)

To add a comma, Space, or Semicolon Separator, replace” ” in this line:

combinedText = combinedText & ws.Cells(i, j).Value & " "

With commas (,), semicolons (😉, or any separator you need.

Example: To add a comma(,) separator, replace it with the following line:

combinedText = combinedText & ws.Cells(i, j).Value & ", "

and for adding a semicolon(;)

combinedText = combinedText & ws.Cells(i, j).Value & ";"

In Excel, the opposite of concatenate is to break the combined text into separate columns. See How to Split Text in Excel.

2. Remove Duplicates While Merging Columns

If you have duplicate values like:

Apple	Banana	Apple
123	123	456
John	Doe	John

and want to remove duplicates while merging, you can write a macro like below:

Sub CombineColumns()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim combinedText As String
    Dim dict As Object
    
    ' Set active worksheet
    Set ws = ActiveSheet
    
    ' Find the last used row and column dynamically
    lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    ' Loop through each row to combine columns
    For i = 1 To lastRow
        combinedText = "" ' Reset string for each row
        Set dict = CreateObject("Scripting.Dictionary") ' Initialize dictionary for unique values
        
        For j = 1 To lastCol
            ' Add cell value if it's not empty and not already in dictionary
            If ws.Cells(i, j).Value <> "" And Not dict.exists(ws.Cells(i, j).Value) Then
                dict.Add ws.Cells(i, j).Value, Nothing
                combinedText = combinedText & ws.Cells(i, j).Value & " "
            End If
        Next j
        
        ' Trim and store in next column
        ws.Cells(i, lastCol + 1).Value = Trim(combinedText)
    Next i
    
    MsgBox "Columns combined successfully!", vbInformation, "Done"
End Sub

When you run this macro, it will remove duplicate values like:

Apple	Banana	Apple	Apple Banana
123	123	456	123 456
John	Doe	John	John Doe

3. Merge Across Multiple Sheets

Modify this line:

Set ws = ActiveSheet

To loop through all sheets:

For Each ws In ThisWorkbook.Sheets

4. Handle Large Datasets Efficiently

Use VBA Arrays instead of looping cell by cell for faster execution.
Optimize memory usage by setting Application.ScreenUpdating = False at the start and True at the end.

5. Combine Only Specific Columns

If you want to combine only specific columns instead of all columns in the sheet, you can do it by defining which columns to include in the macro.

Here is an example macro to combine only A, C, and E Columns.

Sub CombineSpecificColumns()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim combinedText As String
    Dim dict As Object
    Dim columnsToCombine As Variant
    
    ' Set active worksheet
    Set ws = ActiveSheet
    
    ' Define the columns to combine (modify this as needed)
    columnsToCombine = Array(1, 3, 5) ' Example: Column A (1), Column C (3), Column E (5)
    
    ' Find the last used row dynamically
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to combine only selected columns
    For i = 1 To lastRow
        combinedText = "" ' Reset string for each row
        Set dict = CreateObject("Scripting.Dictionary") ' Initialize dictionary for unique values
        
        For j = LBound(columnsToCombine) To UBound(columnsToCombine)
            ' Get column index from the array
            Dim colIndex As Integer
            colIndex = columnsToCombine(j)
            
            ' Add cell value if it's not empty and not already in dictionary
            If ws.Cells(i, colIndex).Value <> "" And Not dict.exists(ws.Cells(i, colIndex).Value) Then
                dict.Add ws.Cells(i, colIndex).Value, Nothing
                combinedText = combinedText & ws.Cells(i, colIndex).Value & " "
            End If
        Next j
        
        ' Trim and store result in **Column F (6th column)**
        ws.Cells(i, 6).Value = Trim(combinedText) ' Column F is the 6th column
    Next i
    
    MsgBox "Selected columns combined successfully!", vbInformation, "Done"
End Sub

How This Modification Works

The columnsToCombine array allows you to specify which columns to merge (e.g., {1, 3, 5} for Columns A, C, and E).
The macro only loops through these columns instead of merging all columns.
The merged result is stored in the next available column without duplicates.

Example:

Input Data

Column A	Column B	Column C	Column D	Column E
John	12345	Doe	NYC	USA
Alice	56789	Smith	LA	USA

If we merge only Columns A, C, and E, the output will be:

Merged Column

John Doe USA

Alice Smith USA

Tips:

Change the columns: Modify columnsToCombine = Array(1, 3, 5) to include different column numbers.
Add a custom separator: Replace ” ” with “, ” or “; ” to separate values differently.
Store results in a specific column: Change ws.Cells(i, UBound(columnsToCombine) + 2).Value to a fixed column like ws.Cells(i, 10).Value.

6. Exclude Specific Words or Values While Merging

If you want to exclude specific words or values while merging, you need to add a list of excluded words and check each value before adding it to the merged result.

VBA Macro to Exclude Specific Words

Sub CombineAllColumnsExcludeWords()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim combinedText As String
    Dim dict As Object
    Dim excludedWords As Variant
    Dim cellValue As String
    Dim word As Variant
    Dim skipValue As Boolean
    
    ' Set active worksheet
    Set ws = ActiveSheet
    
    ' Define words or values to exclude
    excludedWords = Array("Remove", "Smith") ' Modify as needed
    
    ' Find the last used row and last used column dynamically
    lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    ' Loop through each row to combine all columns
    For i = 1 To lastRow
        combinedText = "" ' Reset string for each row
        Set dict = CreateObject("Scripting.Dictionary") ' Dictionary for unique values
        
        For j = 1 To lastCol
            ' Get cell value
            cellValue = Trim(ws.Cells(i, j).Value)
            
            ' Check if the value is in the exclusion list
            skipValue = False
            For Each word In excludedWords
                If StrComp(cellValue, word, vbTextCompare) = 0 Then
                    skipValue = True
                    Exit For
                End If
            Next word
            
            ' Add cell value if it's not empty, not already in dictionary, and not excluded
            If cellValue <> "" And Not dict.exists(cellValue) And Not skipValue Then
                dict.Add cellValue, Nothing
                combinedText = combinedText & cellValue & " "
            End If
        Next j
        
        ' Trim and store result in **Column F (6th column)**
        ws.Cells(i, 6).Value = Trim(combinedText)
    Next i
    
    MsgBox "All columns combined successfully in Column F (excluding specified words)!", vbInformation, "Done"
End Sub

How This Works:

Exclusion List: Modify excludedWords = Array(“N/A”, “Unknown”, “Remove”, “NULL”) to add more words.
Case-Insensitive Matching: The script checks words without case sensitivity (vbTextCompare).
Avoids Duplicates & Excluded Words: Ensures no duplicate values are merged and excluded words are ignored.

Example Usage

Input Data

Column A	Column B	Column C	Column D
John	Remove	Doe	NYC
Alice	Smith	123	LA

Output in Column F (Excluding “Remove” and “Smith”)

Merged Column (F)

John Doe NYC

Alice 123 LA

Pro Tips for Combining Columns Efficiently

Backup Your Data: Always save your work before running VBA macros.
Run on Multiple Sheets: Modify Set ws = ActiveSheet to loop through all sheets.
Handle Dates Properly: Format cells as Text before combining to avoid losing date formats.
Skip Empty Cells: The macro already ignores blank cells, ensuring clean data output.

Alternative Methods: VBA vs Formulas vs Power Query

Method	Best For	Pros	Cons
TEXTJOIN Formula	Simple merging	No VBA needed, dynamic updates	Limited in older Excel versions
Power Query	Large datasets	Works across multiple sheets	Requires setup
VBA Macro	Automation	Fast, flexible, removes duplicates	Requires enabling macros

Download the Excel Practice Sheet (Hands-on Learning!)

Want to practice merging columns in Excel with real-world data? Download this interactive practice sheet and try it yourself!

Download Combine Multiple Column Excel Practice Sheet

Combine Multiple Columns FAQs

1. Can I replace text in password-protected files?

No, unless you modify the macro to unlock the workbook before replacing text.

2. How can I replace text only in a specific column?

Modify the VBA code like this:

ws.Columns(“B:B”).Replace What:=”TechCorp”, Replacement:=”NextGen Supplies”, LookAt:=xlPart

3. Can this macro work for CSV files?

No, but you can first convert CSV files to Excel, run the macro, and save them back as CSV.

4. Is there a way to remove duplicates while merging?

Yes, you can remove duplicates automatically by using a dictionary-based approach.

Want help combining multiple columns with a VBA macro? Or looking for more advanced Excel tricks? Drop a comment below! I’d love to help.

Aravind

Hi, I’m Aravind, a seasoned Automation Test Engineer with 17+ years of industry experience. I specialize in tools like Selenium, JMeter, Appium, and Excel automation. Through this blog, I share practical tutorials, tips, and real-world insights to help testers and developers sharpen their automation skills.