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!

You also want Application.ScreenUpdating = True at the end, not False. — BigBen
– BigBen, Commented Nov 1, 2018 at 17:51
May Please try to loop from lower end to upper end of the range in sheet1 — Ahmed AU
– Ahmed AU, Commented Nov 1, 2018 at 17:57
Why set it to true, when you never set it to false in the first place? There is a misconception that you need to set screenupdating back to true, it automatically sets itself back to true when the sub is over. — Davesexcel
– Davesexcel, Commented Nov 1, 2018 at 17:58
When you delete something you should go backwards since you are changing your original range. — Wizhi
– Wizhi, Commented Nov 1, 2018 at 17:59

Davesexcel · Accepted Answer · 2018-11-01 18:30:21Z

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

Collectives™ on Stack Overflow

Excel VBA Object Required Error at run-time

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related