Help with Macro - Delete all except criteria

[thedeadzeds]

Hi Guys,

Could someone help me with the below. I have recorded this Macro but i need to adapt it so it filters and deletes all rather than look at specific rows.

I essentially want to select 6 worksheets in total, filter by column D and delete anything that does not show 'New Calls'

Thanks in advance

Sub LEAVE_NEW_CALLS_TEST()'
' LEAVE_NEW_CALLS_TEST Macro
'
    Sheets("BA Polk Audi Serv & MOT N Calls").Select
    ActiveSheet.ListObjects("Audi_New_SANDM").Range.AutoFilter Field:=4, _
        Criteria1:=Array("1st Attempt made", "2nd Attempt made", "3rd Attempt Made"), _
        Operator:=xlFilterValues
    Rows("126:126").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=3
    Selection.Delete Shift:=xlUp
    Range("Audi_New_SANDM[[#Headers],[Dealer]]").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Sheets("BA Kerr Audi Serv & MOT N Calls").Select
    ActiveWindow.SmallScroll Down:=-183
    Range("E7").Select


    ActiveSheet.ListObjects("Audi_New_SANDM3").Range.AutoFilter Field:=4, _
        Criteria1:="=1st Attempt made", Operator:=xlOr, Criteria2:= _
        "=3rd Attempt Made"
    Rows("6:6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ListObjects("Audi_New_SANDM3").Range.AutoFilter Field:=4
    Range("C14").Select
    Sheets("CA Polk Audi Serv & MOT N Calls").Select
    Range("Audi_New_SANDM6[[#Headers],[Dealer]]").Select
    Selection.AutoFilter
    Range("D11").Select


    ActiveSheet.ListObjects("Audi_New_SANDM6").Range.AutoFilter Field:=4, _
        Criteria1:=Array("1st Attempt made", "2nd Attempt made", "3rd Attempt Made"), _
        Operator:=xlFilterValues
    Rows("111:111").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("D120").Select
    ActiveSheet.ListObjects("Audi_New_SANDM6").Range.AutoFilter Field:=4
    ActiveWindow.SmallScroll Down:=-18
    Sheets("CA Kerr Audi Serv & MOT N Calls").Select
    Range("D10").Select
    Sheets("BAA Polk Audi Serv & MOT N Call").Select
    Range("D3").Select


    ActiveSheet.ListObjects("Audi_New_SANDM16").Range.AutoFilter Field:=4, _
        Criteria1:=Array("1st Attempt made", "2nd Attempt made", "3rd Attempt Made"), _
        Operator:=xlFilterValues
    Rows("54:54").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("Audi_New_SANDM16[[#Headers],[Dealer]]").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("C7").Select
    Windows("Sales Weekly Data.xlsm").Activate
End Sub