Filter Date via VBA - European Format

[AECJohn]

This is my current script:

Sub FilterDateRange()
'
' FilterDateRange Macro
' Filter a date range
'
' Keyboard Shortcut: Ctrl+Shift+P
'

Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Selection.AutoFill Destination:=Range("I2:I" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("I:I").Copy
Range("I:I").PasteSpecial Paste:=xlPasteValues
Range("L4:N4").NumberFormat = "MM/DD/YYYY"
Range("L4:N4").NumberFormat = "DD/MM/YYYY"


Set First_Cell = Worksheets("Sheet1").Range("A2")
Set Starting_Date = Range("L4")
Set Ending_Date = Range("M4")
Field = 9

First_Cell.AutoFilter Field:=Field, Criteria1:=">=" & Starting_Date, Operator:=xlAnd, Criteria2:="<=" & Ending_Date

Range("I:I").NumberFormat = "MM/DD/YYYY"

End Sub

I cannot get Excel to acknowledge that 01/06/22 is June 1st, it keeps defaulting to January 6th. I have even added lines of code specifying the date format, does not work.
It will acknowledge 25/05/22 as May 25th though... Any date that can be an North American date, it defaults to North American.

Any ideas?

 

[John_w]

Try:

First_Cell.AutoFilter Field:=Field, Criteria1:=">=" & CLng(Starting_Date), Operator:=xlAnd, Criteria2:="<=" & CLng(Ending_Date)

 

[AECJohn]

Amazing, thank you!