Excel VBA Object Required Error at run-time

Question

I am very new to VBA and just beginning to learn. I have a code here to match all the cells of the "M" column in Sheet1 and Sheet3, and delete all the rows from Sheet1 that contain any value from Sheet3's "M" column. If I go through it using F8, I do not get any error but when I assign it to a button, it fails at run-time with 'Object Required' error. I tried a few things that I found online, but none of them seem to work. Below is my code. Any help would be much appreciated.

Sub DeleteRows()

    Dim rng As Range
    Dim rng2 As Range
    Dim cell As Object
    Dim cell2 As Object

    Set rng = Sheets("Sheet1").Range("M2:M1541")
    Set rng2 = Sheets("Sheet3").Range("M2:M30")

    For Each cell In rng
        For Each cell2 In rng2
            If cell.Value = cell2.Value Then
                cell.EntireRow.Delete
            End If
        Next
    Next
    Application.ScreenUpdating = True

End Sub

Thanks in advance!

Answer 1

You can loop through the cells in sheet2 and filter for those items in sheet1. Then you would not be double looping.

Sub Button1_Click()
    Dim ws As Worksheet, sh As Worksheet
    Dim LstRw As Long, Rng As Range, Frng As Range, c As Range, Nrng As Range

    Set ws = Sheets("Sheet2")
    Set sh = Sheets("Sheet1")

    With ws
        LstRw = .Cells(.Rows.Count, "M").End(xlUp).Row
        Set Rng = .Range("M2:M" & LstRw)
    End With

    With sh
        Set Frng = .Range("M2:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
        For Each c In Rng.Cells
            .Range("M1").AutoFilter Field:=1, Criteria1:=c

            On Error Resume Next
            Set Nrng = Frng.SpecialCells(xlVisible)
            On Error GoTo 0
            If Nrng Is Nothing Then
            Else
                Frng.EntireRow.Delete
            End If
        Next c
        .AutoFilterMode = False
    End With

End Sub