How to Filter Out Multiple Criteria Using Excel VBA

Questions:

I have 8 variables in columns A, 1,2,3,4,5, and A, B, C.
My aim is to filter out A, B, C and display only 1-5.

I can do this using the following code:

My_Range.AutoFilter Field:=1, Criteria1:=Array("1", "2", "3","4","5"), _
    Operator:=xlFilterValues

But what the code does is filters variables 1 to 5 and display them.

I want to do the opposite but yield the same result, by filtering out A, B, C and showing variables 1 to 5. What is the solution?

It is possible to filter values not equal to some criteria, but only up to two values that don’t work for this situation:

Range.AutoFilter Field:=1, Criteria1:="=<>A", Operator:=xlOr, Criteria2:="=<>B"

There are a couple of workarounds possible depending on the exact problem:

Use a "helper column" with a formula in column B and then filter on that - e.g. =ISNUMBER(A2) or =NOT(A2="A", A2="B", A2="C") then filter on TRUE
If you can't add a column, use autofilter with Criteria1:=">-65535" (or a suitable number lower than any you expect) which will filter out non-numeric values - assuming this is what you want
Write a VBA sub to hide rows (not exactly the same as an autofilter but it may suffice depending on your needs).
For example:

Public Sub hideABCRows(rangeToFilter As Range)
  Dim oCurrentCell As Range
  On Error GoTo errHandler

  Application.ScreenUpdating = False
  For Each oCurrentCell In rangeToFilter.Cells
    If oCurrentCell.Value = "A" Or oCurrentCell.Value = "B" Or oCurrentCell.Value = "C" Then
      oCurrentCell.EntireRow.Hidden = True
    End If
  Next oCurrentCell

  Application.ScreenUpdating = True
  Exit Sub

errHandler:
    Application.ScreenUpdating = True
End Sub



Ref: https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba