Autofilter error (multiple criterias) 1

[notuserfriendly]

Hi I'm having problems with
this

    ThisWorkbook.ActiveSheet.EnableAutoFilter = True

    If ThisWorkbook.Worksheets("Sheet4").AutoFilter Is Nothing Then

        Rows("1:1").Select
        Selection.AutoFilter
    End If
    
    Selection.AutoFilter Field:=1, Criteria1:="<>*XX*", Operator:=xlOr, Criteria2:="<>*DG*"
     
    'A2 && A3 Nonblanks
    Selection.AutoFilter Field:=2, Criteria1:="<>"
    Selection.AutoFilter Field:=3, Criteria1:="<>"
    
    Selection.AutoFilter Field:=4, Criteria1:="=P*", Operator:=xlOr, Criteria2:="=AB"
    Selection.AutoFilter Field:=21, Criteria1:="Game"

Sometime it works and sometimes it doesn't and that's not that fine. When the error happens I get error 1004:
Autofilter Method of Range class failed. it gets stuck in
one of the selection fields.

Any ideas?

 

[firefytr]

Hi,

Maybe you could try without the Select's ...

    Worksheets("Sheet4").AutoFilterMode = False
    With Worksheets("Sheet4").Range("1:1")
        .AutoFilter Field:=1, Criteria1:="<>*XX*", Operator:=xlOr, Criteria2:="<>*DG*"
        .AutoFilter Field:=2, Criteria1:="<>"
        .AutoFilter Field:=3, Criteria1:="<>"
        .AutoFilter Field:=4, Criteria1:="=P*", Operator:=xlOr, Criteria2:="=AB"
        .AutoFilter Field:=21, Criteria1:="Game"
    End With

HTH

-----------
Regards,
Zack Barresse

 

[notuserfriendly]

No It didn't help. Get the same error.
Does the filter only work if you have a value after doing the first filter?
Does one have to disable events or?

Kind of clueless on this one

 

[notuserfriendly]

I noticed that the error emerges when
filtering the second field. If I change the order of the filterings. It's always on the second filter that creates
the error.

 

[notuserfriendly]

found it:

http://www.contextures.com/xlautofilter03.html

but:

If ActiveSheet.AutoFilterMode Then
   ActiveSheet.AutoFilterMode = False
End If

 

[notuserfriendly]

What I really had a problem with was doing it like this. I think, that's why I get the error. Want to use Ws because there are many sheets => less code.

Dim Ws      As Worksheet

 Set Ws = ThisWorkbook.Worksheets("Sheet4")

    If Ws.AutoFilterMode Then
        Ws.AutoFilterMode = False
    End If
    
    With Ws.Range("1:1")
     ... do filterings
    End With

 

[firefytr]

Did you read the first line of code in my post? Yes, that would give you an error.

-----------
Regards,
Zack Barresse

 

[notuserfriendly]

You're right about that, but since I kept getting errors
I didn't find it too helpful although it helped, what I had a problem with then was using Ws. I kept on getting the same error.
The last posted code did not work without errors.
Tried to use

Set Ws = Thisworkbook.Worksheets(SheetName)

As soon as I stopped using the Ws object it worked.

 

[firefytr]

Hmm, is it the same worksheet? Is the code in a module which resides in the workbook that contains the desired worksheet? As long as it's referenced correctly it should work. But then again, if you have it working, maybe you shouldn't mess with it - as long as the circumstances do not change.

-----------
Regards,
Zack Barresse