Autofilter exceptions with more than two criteria

Question

I'm trying to use my macro to filter my data, I want to allow everything except for three criteria. My code works with two criteria but it won't allow me to add a third and I can't use the Criteria1:=Array function for some reason. My code is:

wsO.Range("A1").AutoFilter Field:=36, Criteria1:="<>Accept as Medicare product", Criteria2:="<>Accept as NJ Medicaid product", Criteria3:="<>Accept as Medicaid product", Operator:=xlFilterValues

Which returns a name argument not found (because I believe you can only add 2 criteria this way)

I have also tried:

wsO.Range("A1").AutoFilter Field:=36, Criteria1:=Array( _
"<>Accept as Medicare product", "<>Accept as NJ Medicaid product", "<>Accept as Medicaid product"), Operator:=xlFilterValues

Which returns me an error of: Run time Error '1004': Application-defined or object-defined error.

Answer 1

You can loop through the range and hide the rows.

Sub OhYa()
    Dim rws As Long
    Dim rng As Range
    Dim f As Range
    Dim a As String, b As String, c As String

    a = "Accept as Medicare product"
    b = "Accept as NJ Medicaid product"
    c = "Accept as Medicaid product"

    rws = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & rws)
    Application.ScreenUpdating = 0

    For Each f In rng.Cells
        If f <> a And f <> b And f <> c Then
            f.EntireRow.Hidden = 1
        End If
    Next f


End Sub