Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

XL2000: Recorded AutoFilter Macro Does Not Filter Dates Correctly


View products that this article applies to.

This article was previously published under Q248235

↑ Back to the top


Symptoms

When running a recorded macro that uses the AutoFilter feature, a date that meets the filter criteria is not properly filtered, either appearing when it shouldn't or not appearing when it should.

↑ Back to the top


Cause

This behavior occurs when you do the following:
  1. While recording a macro, you use the AutoFilter command.
  2. You select a date for part of the AutoFilter criteria.
  3. You change the date format of the cell used for the criteria.
  4. You run the macro you recorded.

↑ Back to the top


Resolution

Change the value assigned to the Criteria1 argument or the Criteria2 argument of the AutoFilter method to match the date format of the date column on the AutoFilter list.

For example, if the line of code is as follows
Selection.AutoFilter Field:=1, Criteria1:="2/2/98"
				
and the cell containing the date in the AutoFilter list is formatted as m/d/yyyy, change the line of code to the following:
Selection.AutoFilter Field:=1, Criteria1:="2/2/1998"
				

↑ Back to the top


More information

The AutoFilter feature performs a simple string comparison. When you change the date format of the cell, AutoFilter fails because the string value is now different from the string value recorded for the AutoFilter method.

↑ Back to the top


References

For more information about the AutoFilter method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type AutoFilter Method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top


Keywords: KB248235, kbprb, kbdtacode

↑ Back to the top

Article Info
Article ID : 248235
Revision : 5
Created on : 10/10/2006
Published on : 10/10/2006
Exists online : False
Views : 234