VBA-Autofilter with text not working when excluding

[JaapZ]

Good afternoon, finding myself in a "smash head on keyboard to continue"-situation.
I'm filtering a dataset on various columns. In one it has to exclude all rows where the cell value in the column reads "Don't use". The cell contents are entered via a formula which looks in another cell.
The formula in the column I'm evaluating (column 23) is"

[I][SIZE=2][FONT=Verdana]=IF('Latest Download'!$Txx=2,'Latest Download'!ALxx,"Don't use")[/FONT][/SIZE][/I]

The filter I'm applying in my macro is the following:

    ActiveSheet.Range("$A$1:$BB$12001").AutoFilter Field:=23, _ 
Criteria1:=Array(<>"*Don't*"), Operator:=xlFilterValues ​

<strike></strike>
​

And that throws me an error.

Stupid thing is that when I change that line so that only the "Dont's" are to be selected, it works correctly.

[I][FONT=Verdana]    ActiveSheet.Range("$A$1:$BB$12001").AutoFilter Field:=23, _
        Criteria1:=Array("*Don't*"), Operator:=xlFilterValues[/FONT][/I]

I've gone through many messages on many forums: no succes.
Anyone who can explain me what I'm doing wrong and what I need to change to make it work?

Regards,

Jaap

 

[Fluff]

Try

ActiveSheet.Range("$A$1:$BB$12001").AutoFilter 23, "<>*Don't*"

 

[JaapZ]

Good afternoon Fluff, that throws me a compile error.

 

[Fluff]

In that case can you please supply the entire code?

 

[JaapZ]

Hello, herewith the total pice of code for that filter.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]' Refresh filter on "Working Document"
    Sheets("Working Document").Select
    ' Remove existing filters on Worksheet "Working Document"
    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
    ' Set new filter
    ActiveSheet.Range("$A$1:$BB$12001").AutoFilter Field:=3, _
        Criteria1:=Array("PC3300", "PC3340", "PC3400", "PC3500"), Operator:=xlFilterValues
    ActiveSheet.Range("$A$1:$BB$12001").AutoFilter Field:=23, _
        Criteria1:=("<>*Don't*"), Operator:=xlFilterValues
    ActiveSheet.Range("$A$1:$BB$12001").AutoFilter Field:=34, _
        Criteria1:=("<>0"), Operator:=xlFilterValues
[/FONT]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
[/FONT]Funny thing is: when I copied the piece for the filter on column 34 and pasted that back to make the filter for column 23 (to make sure that there were no typing errors), I accidentally left the "34".
Means that the "*Don't*"-filter was now applied to column 34 instead of 23. No error. Might it have something to do with the formatting of column 23? That's currently formatted as a date.

 

[Fluff]

With that code you should not be getting a compile error.
What exactly is the message & what is highlighted?

 

[JaapZ]

Ok, I must have been intoxicated, as now it does indeed work as expected. Flabbergasted.
Thanks for answering, as in the end I've got it to work.

 

[Fluff]

Glad it's working & thanks for the feedback