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