MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Weird autofilter results

Posted by chrisB on February 08, 2002 9:02 AM

I am trying to autofilter all records that have 0 value in three fields. The code I am using is:
Selection.AutoFilter field:=17, Criteria1:="-", Operator:=xlAnd
Selection.AutoFilter field:=20, Criteria1:="-", Operator:=xlAnd
Selection.AutoFilter field:=21, Criteria1:="-", Operator:=xlAnd

The first filter works, the second and third return no data. When I use 0 as my criteria I get the same results - any ideas??

Posted by Chris D on February 08, 2002 12:38 PM

is this the same as 20876.html my earlier problem ?

Posted by chrisB on February 08, 2002 1:35 PM

Chris - it sure looks like it's the same - did you ever get resolution (didn't look like it when I read the posts)


Posted by Chris D on February 09, 2002 3:35 AM

nope, but I think Mark was probably on the right track - I seem to remember something was odd about null, or zero values. Longterm, I'm going to have to figure it out as I do like that comma format. I'll try seeing its actions in other functions to see how it behaves, might give a clue

cheers !

Posted by Mark W. on February 09, 2002 3:51 PM

My conclusion was that AutoFilter considers
formatting; hence, after applying the Accounting
format, "-" is in its pick lists rather than the
internal representation, 0.

Posted by Mark W. on February 09, 2002 4:29 PM

Chris, I trust that you understand that you can't
use standard AutoFilters for an OR condition. If
you're testing...

field17="-" OR field20="-" OR field21="-"

You can't use standard AutoFilters, but must
employ an Advanced AutoFilter with a criteria
range defined on a worksheet.