How To Merge Two Excel Spreadsheets And Remove Duplicates Vba

by

How to Merge Two Excel Spreadsheets and Remove Duplicates using VBA

Merging two Excel spreadsheets and removing duplicates can be easily done using Visual Basic for Applications (VBA) in Excel. VBA allows you to automate tasks and perform complex operations on Excel data. Here is a detailed explanation with examples on how to achieve this:

Step 1: Open the Visual Basic Editor

Open your Excel workbook and press Alt + F11 to open the Visual Basic Editor.

Step 2: Insert a New Module

In the Visual Basic Editor, click on Insert and select Module to insert a new module in your workbook.

Step 3: Write the VBA Code

In the newly inserted module, write the following VBA code:


Sub MergeAndRemoveDuplicates()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range

    ' Set the worksheets to merge
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")

    ' Set the ranges to merge in both sheets
    Set rng1 = ws1.Range("A1").CurrentRegion
    Set rng2 = ws2.Range("A1").CurrentRegion

    ' Merge the two ranges into a new range
    rng1.Copy Destination:=rng1.Cells(rng1.Rows.Count + 1, 1)
    rng2.Copy Destination:=rng1.Cells(rng1.Rows.Count + 1, 1)

    ' Remove duplicates from the merged range
    rng1.RemoveDuplicates Columns:=Array(1), Header:=xlYes

    MsgBox "Spreadsheets merged and duplicates removed successfully!"
End Sub

This VBA code defines a subroutine named MergeAndRemoveDuplicates. It declares variables for two worksheets (ws1 and ws2) and two ranges (rng1 and rng2). Modify the code to set the correct worksheet names and ranges for your specific Excel workbook.

The code merges the ranges of both worksheets into a new range within ws1. It then removes duplicates from this merged range based on the values in the first column (Columns:=Array(1)). The Header:=xlYes argument indicates that the first row contains headers.

Step 4: Run the VBA Code

To run the VBA code, go back to Excel and press Alt + Q to exit the Visual Basic Editor. Then, press Alt + F8 to open the Macro dialog.

Select the MergeAndRemoveDuplicates macro and click on Run.

Example

Let’s assume we have two sheets named “Sheet1” and “Sheet2” in our Excel workbook. Both sheets contain data in column A.

Sheet1:

A1
Data 1
Data 2
Data 3

Sheet2:

A1
Data 3
Data 4
Data 5

When we run the VBA code, it will merge the two sheets and remove duplicates, resulting in the following data in Sheet1:

A1
Data 1
Data 2
Data 3
Data 4
Data 5

Note that duplicates are removed, and both sets of data are merged into one.

Leave a comment