Inverse autofilter array of strings-CodePudding

I have an array of strings, and i need to filter my data where the value in column 8 does not equal any of the array values in order to delete entire rows, i'm using the following line of code to filter the data i want to keep, and it's working fine:

ActiveSheet.Range("A1").AutoFilter Field:=8, Criteria1:=arrSubmitters, Operator:=xlFilterValues

and i have the following line to delete entire visible rows which is also working fine:

ActiveSheet.Range("A2", ActiveSheet.Range("A2").End(xlDown).End(xlToRight)).SpecialCells(xlCellTypeVisible).EntireRow.Delete

i need a way to reverse the filter so i can delete visible rows, or a way to delete invisible rows,

knowing that, the unwanted values in column 8 are dynamic so it's complicated to use them to filter, and that i have also tried preceding the array values with the not equal operator "<>" but did not work as they're more than 2

CodePudding user response：

You can copy the visible range to below the filtered rows and then delete all the rows above.

Option Explicit

Sub RevFilter()

    Dim arrSubmitters, lastrow As Long, lastcol As Long
    arrSubmitters = Array("H3", "H11", "H20", "H22")
    
    Application.ScreenUpdating = False
    With ActiveSheet
        lastrow = .Cells(.Rows.Count, 8).End(xlUp).Row
        lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range("A1").AutoFilter Field:=8, Criteria1:=arrSubmitters, Operator:=xlFilterValues
        .Range("A1").Resize(lastrow, lastcol).SpecialCells(xlCellTypeVisible).Copy .Range("A" & lastrow   1)
        Application.CutCopyMode = False
        .Range("A1").AutoFilter
        .Rows("2:" & lastrow   1).Delete ' include copied header
    End With
    Application.ScreenUpdating = True
End Sub