Using 'not equal to' an array as criteria in AutoFilter in Excel VBA

[sharshra]

In my the spread sheet, I have defined a range, which needs to be excluded from the filter. The values to exclude are filled dynamically filled in the range & will be more than 3 in most of the cases. I intend to use the code similar to what is shown below. varFilterValues is an array populated with values from the named range. But I'm struggling on how to exclude these from the filter. I'm sure it must be a silly mistake in defining the 'not equal to' array in Criteria1. Can any one point where the mistake is please? I searched internet but couldn't find simple & appropriate solution.

[B]    ActiveSheet.Range("$A$1:$V$100").AutoFilter Field:=9, _
        Criteria1:[COLOR=#ff0000]<>varFilterValues[/COLOR], _
        Operator:=xlAnd[/B]

I know the following code works when I want to add filter using similar method & define criteria 'equal' to an array. I was expecting similar for 'not equal to' an array condition also.

    ActiveSheet.Range("$A$1:$V$100").AutoFilter Field:=9, _
        Criteria1:[COLOR=#008000]=varFilterValues[/COLOR], _
        Operator:=xlFilterValues

 

[vcoolio]

Hello Sharshra,

Try one of the following methods:-

[B][COLOR=#ff0000]"<>varFilterValues"[/COLOR][/B]

or

[B][COLOR=#ff0000]"<>" & varFilterValues[/COLOR][/B]

Whichever way, the <> needs to be enclosed with quotation marks.

I hope that this helps.

Cheerio,
vcoolio.

 

[sharshra]

Thanks, vcoolio for quick response

[COLOR=#ff0000]"<>" & varFilterValues[/COLOR]

throws an error 'type mismatch'.

[COLOR=#ff0000]"<>varFilterValues"[/COLOR]

is not throwing any error, but it is not actually filtering. It is as good as no filter applied! I think it reads "<>varFilterValues" as just a string & try to filter "<>varFilterValues", which obviously is not there, resulting nothing being filtered.

Is there any other ways to achieve this?

 

[Fluff]

As far as I know, if you want to filter on anything other than = using autofilter, then you are limited to 2 values.
You might want to have a look at advanced filters.
http://www.contextures.com/xladvfilter01.html

 

[jolivanes]

jindon posted this yesterday.

Sub test()
    Dim e
    Application.ScreenUpdating = False
    With Cells(1).CurrentRegion
        For Each e In Array(Array("coupon", 3), Array("blu", 4))
            .AutoFilter 1, "<>*" & e(0) & "*"
            .Columns(e(1)).Offset(1).ClearContents
            .AutoFilter
        Next
    End With
    Application.ScreenUpdating = True
End Sub

You might be able to change it to fit your needs.

 

[Fluff]

jindon posted this yesterday.

Could you please supply a link to the thread?

 

[jolivanes]

https://www.excelforum.com/excel-pr...cell-and-loop-until-last-cell-with-value.html

 

[Fluff]

@jolivanes
Thanks for the link.
@sharshra
It may be possible to modify jindon's approach, depending on what you are try to do.
What are you trying to do with the filtered data?

 

[sharshra]

I modified the code posted by jindon & tried the following. Still getting the error 'Type mismatch'. It erring at &.

Criteria1:="<>*" [COLOR=#ff0000][B]&[/B][/COLOR] varFilterValues & "*"

Hi Fluff,
I have a named range to list the values to remove from the filter. The values in this named range is populated based on different criteria. varFilterValues is an array & contains these values from the named range. When I filter a column, I have to exclude these values stored in varFilterValues.

 

[Fluff]

In that case you will not be able to use autofilter, as you are limited to 2 values.
Have a look at advanced filters