autofilter and vba problem

[Guest]

Using Excel 2003.

I'm stuck! I have several autofilter macros that work just fine. However,
one is not working at all. Here are the details:

1. All cells in the target column are formatted as Text.
2. The column contains text-based numbers (e.g. 001, 013, 279, etc.). So,
001 is actually in the cell, not a 1 formatted with 00 in front.

I want to be able to filter out (exclude) rows containing specific numbers
(e.g. no rows with 001). I can do this autofilter interactively, just fine!
But when I repeat the procedure in vba, it acts as if I've chosen the Show
All command: Nothing is filtered!

Here is my macro:
Range("Database").AutoFilter Field:=16, Criteria1:="<>281",
Operator:=xlAnd, Criteria2:="<>001"

Am I missing the obvious here? Keep in mind that all of my other autofilter
macros work well; but this is the only one that is based on "not equal to".

George

 

[Guest]

I was wrong on one point: It does not work interactively. And I notice that
if I type 001 in the custom criterion text box, Excel changes it to a 1,
anyway.

 

[Guest]

Okay, I figured it out:

Selection.AutoFilter Field:=16, Criteria1:="<>*001*", Operator:=xlAnd, _
Criteria2:="<>*281*"

I used the "does not contain" operator, which does a pattern match. This
works just keen. Text-formatted numbers must confuse Excel. Heck, they
confuse people, too (at least, when working IN Excel!).

So, no replies needed. Hope this Q&A was helpful and on to the next big one.

George

 

[Guest]

well done! Just wait until you try using VBA to filter a column of dates