Excel – Efficient removal of duplicate records across multiple Excel sheets

microsoft excelvba

I have a workbook with 2 sheets. Sheet 1 has 3 columns (1A, 1B and 1C) and sheet 2 has 2 columns (2A and 2B). Now I need to delete rows from sheet 1 if 1A and 1B match any row in sheet 2 (i.e. 1A=2A and 1B=2B). So basically forget about 1C for the moment and delete all rows from sheet 1 that match rows in sheet 2.

One way would be to write a macro that takes each row of sheet 1, and compares 1A with all 2A values. If a match is found then compare the corresponding 1B with 2B and if those are the same too then delete the row from sheet 1. Given that the sheets have more than 100,000 rows each I think this will make the number of comparisons way too large and take a very long time.

Is there a more efficient way of doing this?

Best Answer

This is VBa, there is no undo, so before you do it, take a back up of your file first!

Option Explicit

Sub WalkThePlank()
Application.ScreenUpdating = False

Dim startRow As Integer
startRow = 1

Dim row As Integer
row = startRow

Dim bRow As Integer

'sharks below cap'ain
Do While (Worksheets("Sheet1").Range("A" & row).Value <> "")

    Dim aVal As String
    Dim bVal As String

    aVal = Worksheets("Sheet1").Range("A" & row).Value
    bVal = Worksheets("Sheet1").Range("B" & row).Value


    'I see thy booty
    bRow = startRow

    Do While (Worksheets("Sheet2").Range("A" & bRow).Value <> "")

    Dim aVal2 As String
    Dim bVal2 As String

    aVal2 = Worksheets("Sheet2").Range("A" & bRow).Value
    bVal2 = Worksheets("Sheet2").Range("B" & bRow).Value

    If (aVal = aVal2 And bVal = bVal2) Then

        Worksheets("Sheet1").Rows(row).Delete ' we found a traitor, feed em to the sharks
        row = row - row
        Exit Do

    End If

    bRow = bRow + 1

    Loop

row = row + 1
Loop

End Sub

Screenshots:

Sheet1

enter image description here

Sheet2

enter image description here

And after I run the macro Sheet1 looks like (Sheet2 remains unchanged)

enter image description here

How do I add VBA in MS Office?

Related Question