Filtering an Excel table by period between two dates in VBA-CodePudding

I would like to filter a large Excel table by date in VBA. When I do it by hand with the "Between" filter, it works (see Screenshot 1 and Screenshot 2).

If I run the "Record Macro" function at the same time, it also works and I get the following code:

ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=4, Criteria1:= _
  ">=15/09/2021", Operator:=xlAnd, Criteria2:="<=17/10/2021"

However, when I try to run the resulting macro, it filters out all records:

0 of 29725 records found

Here is what I have tried:

Checked Stack Overflow Questions #40820757 and #23132653.
Checked that the field was indeed in Date format: It is.
Went to the "Change Date and Time formats" menu in Windows and checked the local short date format in my system: "DD/MM/YYYY".
Tried the following code, which gave the same result:


Dim Date1 As Date
Dim Date2 As Date

Date1 = CDate("15/09/2021")
Date2 = CDate("17/10/2021")

ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=4, Criteria1:= _
  ">=" & Date1, Operator:=xlAnd, Criteria2:="<=" & Date2

Do you have any idea of how I can troubleshoot this problem? Thanks.

CodePudding user response：

Try changing the date format as in the code below.

Sub Macro1()
    Date1 = Format("15/09/2021", "mm/dd/yyyy")
    Date2 = Format("17/10/2021", "mm/dd/yyyy")
    
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4, Criteria1:= _
      ">=" & Date1, Operator:=xlAnd, Criteria2:="<=" & Date2
End Sub