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

 

[codeliftsleep]

Hmm...that's strange...I have used an array to autofilter data for data matching the array values, doesn't make much sense why you couldn't use an array to filter out for values not in the array.

Quirky things like this is what drive you crazy with VBA as a programmer...and there are quite a few other types of things that fall into the "things that make you go hmmm..." Category of what you can't do.

 

[codeliftsleep]

Probably easiest solution is to use an array that contains the values you dont want, autofilter those and copy the visible autofilter range to a temp sheet, then take off the autofilter, which will leave you with the data you want...

When you use the array as a criteria make sure you also have Operator:=xlFilterValues

 

[sharshra]

Hi codeliftsleep,
Thanks for the response. I'm using the array to capture the value from names range & trying to use the array to filter out few values. varFilterValues quoted in my posts is an array only. But, the problem is that I couldn't find a way to use 'not equal to array' in the field Criteria1. It is simple to use equal to in Criteria1, but I'm wondering why is it so complicated / difficult to use 'not equal to'.

 

[codeliftsleep]

Hi codeliftsleep,
Thanks for the response. I'm using the array to capture the value from names range & trying to use the array to filter out few values. varFilterValues quoted in my posts is an array only. But, the problem is that I couldn't find a way to use 'not equal to array' in the field Criteria1. It is simple to use equal to in Criteria1, but I'm wondering why is it so complicated / difficult to use 'not equal to'.

Simply because Microsoft failed to build this into VBA. IMO, they built VBA to a certain point, said "OK, its good enough" and then turned their attention to .Net Framework, VB.net, and C#...

In many ways it just feels "incomplete" with a lot of the workarounds you have to do for what are seemingly simple things.

 

[sharshra]

I'm using an inefficient way to proceed further with my work. If there is any better solution, please suggest.
- List all the values under the named range that should appear in the filter.
- Remove the values from the named range that shouldn't appear in the filter.

I hate this because there could be hundreds of values that should appear in the filter & handful (3 to 4) that needs to excluded. It would have been more efficient & effective to exclude those 3-4 values than listing hundreds of values & then remove those 3-4 from the list.

 

[codeliftsleep]

I'm using an inefficient way to proceed further with my work. If there is any better solution, please suggest.
- List all the values under the named range that should appear in the filter.
- Remove the values from the named range that shouldn't appear in the filter.

I hate this because there could be hundreds of values that should appear in the filter & handful (3 to 4) that needs to excluded. It would have been more efficient & effective to exclude those 3-4 values than listing hundreds of values & then remove those 3-4 from the list.

There SHOULD be ways to do this in VBA but there aren't. Why? Because Microsoft discontinued the language in 2007 and its a dead langiage at this point used only in Office products. No further updates to it have or will be released.

You are just going to have to figure something out with what you have to work with.

Your other option is to list the ones you want excluded in an array and then loop through the array and remove them one at a time, autofiltering by each one. That WILL work but it definitely is not ideal in terms of speed.

 

[Fluff]

@sharshra
Have you had a look at advanced filters?

 

[codeliftsleep]

@sharshra
Have you had a look at advanced filters?

@Fluff good call!
@sharshra it appears you CAN do it with advanced filters and here is an example of how to do it right here:

https://www.thespreadsheetguru.com/...automate-filtering-on-and-out-specific-values

 

[Fluff]

@Fluff good call!

@sharshra it appears you CAN do it with advanced filters and here is an example of how to do it right here:

https://www.thespreadsheetguru.com/...automate-filtering-on-and-out-specific-values

That's why I said it in post#4

 

[sharshra]

Wishing happy new year to all

Thanks Fluff, codeliftsleep for your suggestions. Advance filter is good but it may be difficult to maintain. Hence I decided to go ahead with listing all required values & removing the unwanted ones from the named range. Though this method is less efficient, the maintenance will not take much effort. One will have to just list all the required values in the named range, without having to worry about maintaining additional logic with advance filters.