1

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?

3
  • No, the only way you can determine a match is by searching for a match. Maybe sort each sheet ascending by the same column(s) and then write the search to stop once a match is found. If you're doing vba, remember to application.screenupdating = false You may want to look into transferring the data to a relational database schema, like access Dec 3, 2014 at 12:51
  • Thanks. Will this be faster in Access? @Raystafarian
    – Jason
    Dec 3, 2014 at 12:58
  • 1
    In general, databases will handle large amounts of data better, whether or not it will be faster depends on the data, the machine, etc. Dec 3, 2014 at 13:07

1 Answer 1

1

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?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .