Select reverse filter

Question

Criteria1:=Array( _
    "0.4", "0.6", "0.2", "50", "12-06-20", "9", "2", "25", "Chosen", "gm", "GrpQ3", "USD","Not Chosen", "questions skiped', "=") _
    , Operator:=xlFilterValues

In the above filter, I need all other value except "Not Chosen", "questions skiped', "=" but my set of values changes every time and in each set the above mentioned attributes present in common. So, in which way I have to change my code, in order to select all the values every time I given new values apart from above three.

Can anyone help me with this.

You can't use `AutoFilter` in this way for more than two criteria... see for example https://stackoverflow.com/questions/19497659/excel-vba-autofilter-all-but-three. — BigBen, Jun 18 '20 at 18:02
@BigBen The syntax he is using is how one selects multiple values in the autofilter -- eg: checking off multiple checkboxes in the filter dropdown. The two criteria limitation applies to using Formulas — Ron Rosenfeld, Jun 18 '20 at 18:49
@RonRosenfeld correct. I was thinking of using formulas with `<>` and the values to be excluded. — BigBen, Jun 18 '20 at 18:52

score 0 · Answer 1 · answered Jun 18 '20 at 18:41

Presumably your criteria are coming from a range of values in your range to be filtered.

So try creating an array that includes all of the values in that range except for the one's you want to omit. eg:

    Dim myRange As Range
    Dim arrSrc As Variant, arrCrit() As String
    Dim I As Long, V

Set myRange = Range('the column being filtered on')

arrSrc = myRange

I = 0
ReDim arrCrit(1 To UBound(arrSrc))
    For Each V In arrSrc
        Select Case V
            Case "Not Chosen", "=", "questions skiped"
            'do nothing
        Case Else
            I = I + 1
            arrCrit(I) = V
        End Select
    Next V

ReDim Preserve arrCrit(1 To I)

Then, in your filter code, you can:

Criteria1:= arrCrit

Select reverse filter

1 Answers1