VBA to deselect specific value from a column filter but not exclude them

[Martin sherk]

Is there a way to deselect specific values in filtered column C but not deleting or excluding them, just deslect them

My code:

Sub mai()
With ActiveWorkbook.ActiveSheet.Range("A1")
 ActiveSheet.AutoFilterMode = False
.AutoFilter Field:=9, Criteria1:="#N/A"
.AutoFilter Field:=8, Criteria1:="FALSE"
'.AutoFilter Field:=3, Criteria1:="<>A"
'.AutoFilter Field:=3, Criteria1:="<>EE"
'.AutoFilter Field:=3, Criteria1:="<>EF"
'.AutoFilter Field:=3, Criteria1:="<>AA"
'.AutoFilter Field:=3, Criteria1:="<>HC"
'.AutoFilter Field:=3, Criteria1:="<>HR"

If ActiveWorkbook.ActiveSheet.AutoFilterMode = False Then
MsgBox "There are no filtered rows"
Exit Sub
End If
End sub

 

[sijpie]

If you search the web on excluding multiple criteria using VBA you see that it is only possible with workarounds, like adding an extra (hidden) column that has a simple IF() formula to give TRUE when the criteria do not occur in that row.

The other thing you can do with the latest versions of Excel is use the =FILTER() function to create the table without VBA.

The easiest to work with formulas is to convert your table to an Excel table if you haven't done that already. It makes the formulas easier to read, and it makes everything dynamic: if you add data to the table, all formulas and graphs referring to the table get updated automatically.

So assuming you have converted your table to an Excel table and the name (default) is Table1, then in another section of the worksheet (or in another worksheet), select a cell and put the following formula there: (NOTE: 'Column3' is the header name of column C, adjust this)

=FILTER(Table1,(Table1[Column3]<>"A")*(Table1[Column3]<>"EE")*(Table1[Column3]<>"EF")*(Table1[Column3]<>"AA")*(Table1[Column3]<>"HC")*(Table1[Column3]<>"HR"))

The filtered table will appear.