Problem with multiple criteria in VBA

[Klash Ville]

I have the following code:

.Range.AutoFilter Field:=.ListColumns("Type").Index, Criteria1:="=Phone", Operator:=xlAnd, Criteria2:="=Router", Operator:=xlAnd, Criteria3:="=Telecommunications Equipment", Operator:=xlAnd, Criteria4:="=Network Product", Operator:=xlAnd, Criteria5:="=Headset"

When running this code, the following error message returns:


Run time error '91':

Object Variable or With Block variable not set

 

[Joe4]

Please post the rest of your code.
That error message usually indicates that you have not properly closed out of a WITH, IF THEN, or LOOP statement.
We would need to see the rest of the code to see what might be missing.

 

[Klash Ville]

Please post the rest of your code.
That error message usually indicates that you have not properly closed out of a WITH, IF THEN, or LOOP statement.
We would need to see the rest of the code to see what might be missing.

With ActiveSheet.ListObjects("Table2")
        .Range.AutoFilter Field:=.ListColumns("UK Category").Index
        .Range.AutoFilter Field:=.ListColumns("Description").Index
        .Range.AutoFilter Field:=.ListColumns("Type").Index, Criteria1:="=Phone", Operator:=xlAnd, Criteria2:="=Router", Operator:=xlAnd, Criteria3:="=Telecommunications Equipment", Operator:=xlAnd, Criteria4:="=Network Product", Operator:=xlAnd, Criteria5:="=Headset"
End With

It's a very simple With and End With, I have used so many times before, but, it's the first time I am filtering for 5 values, so... I guess it's because of the size?

 

[Klash Ville]

I decided to only use the filtering of the 5 values, without the 2 filtering above, the following error returns:

Run time error '1004':

Application-defined or object-defined error

 

[Joe4]

Does it work with just 2 filters?
If you still get the same error message, then I don't suspect the error is actually with this part of the code, but with you "bigger" code.
I am not 100% certain, but I think you can filter using at least 3 criteria. I am not certain if you can do more than that. But you should be able to test that pretty easily to see if that is the case (see if 3 works, then see if 4 works).

 

[Klash Ville]

Does it work with just 2 filters?
If you still get the same error message, then I don't suspect the error is actually with this part of the code, but with you "bigger" code.
I am not 100% certain, but I think you can filter using at least 3 criteria. I am not certain if you can do more than that. But you should be able to test that pretty easily to see if that is the case (see if 3 works, then see if 4 works).

Yap, it works with only 1 filter, and 2 filters maximum. With 3, 4, 5 etc... it throws error... I guess it's because the xlAnd only works with 2? =/

 

[Joe4]

You may need to use Advanced Filters to do more than 2.

 

[Klash Ville]

You may need to use Advanced Filters to do more than 2.

I never used such thing before, where can I find how to use that?

 

[Joe4]

I never used such thing before, where can I find how to use that?

Do a Google Search, and there are lots of tutorials available on it.

 

[Klash Ville]

Do a Google Search, and there are lots of tutorials available on it.

Just to be 100% sure, I need to apply this sort of advanced filtering logic?

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