Delete filtered rows - 2 conditions

[roxnoxsox]

Hi all,

Sorry I'm just getting a bit tangled in this VBA coding so any help is much appreciated. I have a long list of numbers in column A. I'm trying to write a macro which will delete all rows that do not contain 87036 or 120317 in column A. However, I can't seem to make this work? This is coming up with an error message but I can't see what I'm missing

This is the code I am currently using:

     LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("A2:A" & LastRow).AutoFilter Field:=1, Criteria1:= _
        "<>87036", Operator:=xlOr, Criteria2:="<>120317"
With ActiveSheet.AutoFilter.Range
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With
    ActiveSheet.ShowAllData
    Selection.AutoFilter

I can make this work for ONE number (ie. Either 87036 OR 120317) but I can't make it work where any rows with BOTH of these numbers are kept. See below code which I can use for ONE number:

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("A2:A" & LastRow).AutoFilter Field:=1, Criteria1:="<>87036"
With ActiveSheet.AutoFilter.Range
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With
    ActiveSheet.ShowAllData
    Selection.AutoFilter

Any help is much appreciated!

[mdmackillop]

Option Explicit
Sub Test()
    Dim r As Range
    Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    With r
    .AutoFilter Field:=1, Criteria1:="<>87036", Operator:=xlAnd, Criteria2:="<>120317"
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
End Sub