VBA: Delete Rows Based on Criteria

[unknownymous]

Hello Gurus,

Good day!

Could you possibly help me tweak the code below? Scenario is I have to delete the data that is below 500 including blanks and zeroes in Column E. Headers is located in Row A

Sub DeleteRowIF()

Dim zlr As Long
zlr = ActiveSheet.UsedRange.rows.Count
With Range("A2:AZ" & zlr)
.AutoFilter
.AutoFilter Field:=5, Criteria1:="<500"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With


End Sub

Appreciate the help.

 

[rollis13]

This should be how you need it:

Option Explicit
Sub DeleteRowIF()
    Dim zlr    As Long
    zlr = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.AutoFilterMode = False            '<- added
    With Range("A1:AZ" & zlr)                     '<- changed
        .AutoFilter Field:=5, Criteria1:="<500", Operator:=xlOr, Criteria2:="=" '<- changed
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete '<- changed
    End With
    ActiveSheet.AutoFilterMode = False            '<- added
End Sub

 

[unknownymous]

This should be how you need it:

Option Explicit
Sub DeleteRowIF()
    Dim zlr    As Long
    zlr = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.AutoFilterMode = False            '<- added
    With Range("A1:AZ" & zlr)                     '<- changed
        .AutoFilter Field:=5, Criteria1:="<500", Operator:=xlOr, Criteria2:="=" '<- changed
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete '<- changed
    End With
    ActiveSheet.AutoFilterMode = False            '<- added
End Sub

It's working! Thank you.

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.