WORKAROUND
Either change the date format of the recorded macro to the default short date format of the operating system, or format the dates in the worksheet and recorded code with a custom date format.
Method 1: Changing the Date Format of the Recorded Code
Change the value assigned to the
Criteria1 argument or the
Criteria2 argument of the
AutoFilter method to match the default short date format of the operating system.
For example, in Microsoft Windows 95, Microsoft Windows 98, or Microsoft Windows NT 4.0, the line of code is the following
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"
Method 2: Changing the Date Format of the Code and Worksheet Cells
Change the format of the dates to a different custom date number format, and change the value assigned to the
Criteria1 argument or the
Criteria2 argument of the
AutoFilter method to match the same custom date number format. Follow these steps to perform the task:
- In the worksheet, select the dates in the list.
- On the Format menu, click Cells.
- On the Number tab, click Custom in the Category list.
- Type a new date format in the Type box (for example, MM/dd/yyyy) and click OK.
- Change the value assigned to the Criteria1 argument or the Criteria2 argument of the AutoFilter method to match the date format of the dates in the worksheet list.
For example, if the cells are formatted as MM/dd/yyyy, the line of code is the following:
Selection.AutoFilter Field:=1, Criteria1:="02/02/1998"
REFERENCES
For additional information about other issues with dates in AutoFilters and recorded macros, click the article number below
to view the article in the Microsoft Knowledge Base:
215770 XL97: Recorded AutoFilter Macro Does Not Filter Dates Correctly