CODEWITHSUNDEEP
excelvba
Dan
Dan

Reputation: 67

Macro not deleting all items that meet requirement

I wrote this very simple macro to delete all rows when Column P has a "1" or "0" and Column L contains "False". For what ever reason, it does not seem to run continuously. I have to repeatedly run the macro over and over to delete everything.

    Sub Delete_rows()

    Dim Pcell As Range
    Dim LastPCell As Long
    Range("P2", Range("P65000").End(xlUp)).Name = "LastPCell"

    For Each Pcell In Range("LastPCell")
    If Pcell <= 1 And Pcell.Offset(0, -4) = "False" Then Pcell.Offset(0, 4).EntireRow.Delete
    Next Pcell

    End Sub

There only about 10,000 rows so the range size should be fine.

I'm sorta dumbfound at this point, I haven't been able to trouble shoot it. Any ideas?

Thanks.

Upvotes: 1

Views: 171

Answers (1)

Brad
Brad

Reputation: 12253

To Expand on @bernie's answer

Sub Delete_rows()
Dim lastCell As Long, i As Integer, Pcell As Range
lastCell = ActiveSheet.Range("P65000").End(xlUp).Row

For i = lastCell To 2 Step -1
Set Pcell = ActiveSheet.Cells(i, 16)
    If Pcell <= 1 And Pcell.Offset(0, -4) = "False" Then
        Pcell.Offset(0, 4).EntireRow.Delete
    End If
Next i

End Sub

Upvotes: 3

Related Questions