The Range AdvancedFilter Method

The AdvancedFilter method does play by the rules, but in a way that may be undesirable. The criteria used for filtering are entered on the worksheet in the criteria range. In a similar way to AutoFilter, the criteria string includes an operator and a value. Note that when using the equals operator, AdvancedFilter correctly matches by value and hence differs from AutoFilter in this respect.

Because this is entirely within the Excel domain, the string must be formatted according to the Windows Regional Settings to work, which poses a problem when matching on dates and numbers. An advanced filter search criterion of ">1.234" will find all numbers greater than 1.234 in the U.S., but all numbers greater than 1234 when run in Norway. A criterion of ">02/03/2 007 " will find all dates after February 3 in the U.S., but after March 2 in Europe.

The only workarounds are to populate the criteria strings from code, before running the AdvancedFilter method, or to use a calculated criteria string, using the =TEXT trick mentioned previously. Instead of a criterion of ">=02/03/2007", to find all dates on or after February 3, 2007, you could use this formula:

=">="&TEXT(DATE(2007,2,3),DateFormat)

Here, DateFormat is a reference to a cell that has been set to a local date format. If the date is an integer (does not contain a time component), you could also just use the criteria string ">=39116", and hope that the user realizes that 39116 is actually February 3, 2007.

0 0

Post a comment