I have 8 variables in column 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 it filters variables 1 to 5 and displays them.
I want to do the opposite, but yielding the same result, by filtering out A, B, C and showing variables 1 to 5
I tried this code:
My_Range.AutoFilter Field:=1, Criteria1:=Array("<>A", "<>B", "<>C"), _
Operator:=xlFilterValues
But it did not work.
Why cant I use this code ?
It gives this error:
Run time error 1004 autofilter method of range class failed
How can I perform this?
I think (from experimenting - MSDN is unhelpful here) that there is no direct way of doing this. Setting Criteria1
to an Array
is equivalent to using the tick boxes in the dropdown - as you say it will only filter a list based on items that match one of those in the array.
Interestingly, if you have the literal values "<>A"
and "<>B"
in the list and filter on these the macro recorder comes up with
Range.AutoFilter Field:=1, Criteria1:="=<>A", Operator:=xlOr, Criteria2:="=<>B"
which works. But if you then have the literal value "<>C"
as well and you filter for all three (using tick boxes) while recording a macro, the macro recorder replicates precisely your code which then fails with an error. I guess I'd call that a bug - there are filters you can do using the UI which you can't do with VBA.
Anyway, back to your problem. It is possible to filter values not equal to some criteria, but only up to two values which doesn't work for you:
Range("$A$1:$A$9").AutoFilter Field:=1, Criteria1:="<>A", Criteria2:="<>B", Operator:=xlAnd
There are a couple of workarounds possible depending on the exact problem:
=ISNUMBER(A2)
or =NOT(A2="A", A2="B", A2="C")
then filter on TRUE
Criteria1:=">-65535"
(or a suitable number lower than any you expect) which will filter out non-numeric values - assuming this is what you wantFor 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