AutoFilter Working but not on Date

[airforceone]

I'm trying to Autofilter my record and works well with Non-Date criteria and on recorded Macro (with Date Criteria)
but not with Date criteria selected from a covered period.
I attached the sample Workbook and my sample code here

What I'm trying to do here is to be able to autofilter and select From and To Date (Covered Period) and Client Remarks (Col D)

Option Explicit
'
'   using CalendarForm @ https://trevoreyre.com/portfolio/excel-datepicker/
'
Private Sub btnStart_Click()
    Dim StartDate As Date
    StartDate = CalendarForm.GetDate
    txtStart.value = Format(StartDate, "yyyy.mm.dd")
    
End Sub
Private Sub btnEnd_Click()
    Dim EndDate As Date
    EndDate = CalendarForm.GetDate
    txtEnd.value = Format(EndDate, "yyyy.mm.dd")
End Sub

Private Sub btnFILTER_Click()
    Dim LastRow As Long
    LastRow = Worksheets("TEMP").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'   Working Showing Filtered Data
    Worksheets("TEMP").Range("A2:AS" & LastRow).AutoFilter Field:=4, Criteria1:="BU*"

'   Working But not showing Expected Result
    Worksheets("TEMP").Range("A1:D" & LastRow).AutoFilter Field:=2, Operator:=xlFilterValues, _
    Criteria1:=">=" & txtStart, Operator:=xlAnd, Criteria2:="<=" & txtEnd

'   Code from Macro Recording
    ActiveSheet.Range("$A$1:$D$390").AutoFilter Field:=2, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "1/31/2023", 1, "5/31/2023")
    ActiveSheet.Range("$A$1:$D$390").AutoFilter Field:=4, Criteria1:="BUYER"
End Sub

 

[dmt32]

Hi,
Dates and filters can be problematic what works for some, may not work for others

assuming the dates in the range are real dates, see if update to your code below resolves your issue

Worksheets("TEMP").Range("A1:D" & LastRow).AutoFilter Field:=2, Operator:=xlFilterValues, _
    Criteria1:=">=" & CLng(StartDate), Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)

Dave

 

[Alex Blakenburg]

With your current code I don't think you will have access to the variables StartDate end EndDate in Dave's @dmt32 modification.
If you find it doesn't work for you and you don't want to set them up as Public Variables, try replacing this:

'   Working But not showing Expected Result
    Worksheets("TEMP").Range("A1:D" & LastRow).AutoFilter Field:=2, Operator:=xlFilterValues, _
    Criteria1:=">=" & txtStart, Operator:=xlAnd, Criteria2:="<=" & txtEnd

With this:

    dtStart = CLng(CDate(Replace(txtStart, ".", "-")))
    dtEnd = CLng(CDate(Replace(txtEnd, ".", "-")))
    Worksheets("TEMP").Range("A1:D" & LastRow).AutoFilter Field:=2, Operator:=xlFilterValues, _
                   Criteria1:=">=" & dtStart, Operator:=xlAnd, Criteria2:="<=" & dtEnd

 

[dmt32]

With your current code I don't think you will have access to the variables StartDate end EndDate in Dave's @dmt32 modification.

@Alex Blakenburg - thanks for catch Alex only glanced at OPs code & thought was reading one procedure.
Dave

 

[airforceone]

Big thanks to to you guys @Alex Blakenburg & @dmt32

 

[Alex Blakenburg]

You're welcome. Glad we could help.