The Range AutoFilter Method

The AutoFilter method of a Range object is a very curious beast. You are forced to pass it strings for its filter criteria, and hence you must be aware of its string handling behavior. The criteria string consists of an operator (=, >, <, >=, and so on) followed by a value. If no operator is specified, the equals operator (=) is assumed.

The key issue is that when using the equals operator, AutoFilter performs a textual match, whereas using any other operator results in a match by value. This gives you problems when trying to locate exact matches for dates and numbers. If you use equals, Excel matches on the text that is displayed in the cell — that is, the formatted number. Because the text displayed in a cell will change with different regional settings and Windows language versions, it is impossible for you to create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to U.S. formats. Hence, a search criterion of ">=02/01/2007 " will find all dates on or after February 1, 2007, in all locales. You can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on February 1, 2007 and will work in any locale:

Range("A1:D200").AutoFilter 2, ">=02/01/2007", xlAnd, "<=02/01/2007"

0 0

Post a comment