Filter OUT via Array

[niluj]

Hello Mr Excel Message board! glad to meet you.

I am trying to filter information that I do not want to see (rather than filter FOR information).

I'm trying to do this by using an array, here is the example

Sub FilterOutFromArray()
Dim List() As Variant

List = Array("example1", "example2", _
"example3", "example4", _
"example5", "example6")
    
With ActiveSheet.Range("A:K")
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:=Array(List), Operator:=xlFilterValues
End With    
End Sub

Notice the "Criteria1:=Array(List)" section. Is there syntax to filter for what does NOT equal to the array? Just as an example (this doesn't work) something like this: "Criteria1:=<>Array(List)"

 

[niluj]

Anyone have any idea if <> array is possible?

 

[Andrew Poulsom]

No, you would have to filter for those items that aren't in your list. You can't use not equals with xlFilterValues as the Operator.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG20Apr01
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] List [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
List = Array("example1", "example2", _
"example3", "example4", _
"example5", "example6")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(List)
        [COLOR="Navy"]If[/COLOR] Dn = List(n) [COLOR="Navy"]Then[/COLOR]
            Dn.EntireRow.Hidden = True
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick