VBA filter by date

0
votes

In Excel VBA, default date input format is "mm/dd/yyyy". How can I change "yyyy/mm/dd" format to "mm/dd/yyyy" so Excel still recognizes it as date?

Whenever I change input to "yyyy/mm/dd" and later format it as "mm/dd/yyyy" (with CDATE or Format functions) Excel doesn't filter data. Does anyone know a workaround? Thanks!

sub date_filter()

lastrow = Sheets("Sheet2").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

dataSTART = InputBox("Please enter start date (example 01/01/2014)")
dataEND = InputBox("Please enter end date (example 01/28/2014)")

Range(Cells(5, 2), Cells(lastrow, 2)).AutoFilter Field:=2, Criteria1:= _
        ">=" & dataSTART, Operator:=xlAnd, Criteria2:="<=" & dataEND

end sub
1
excelvba

1 Answers

1
votes

Convert the dates to Double (I assume your variables are declared as date):

Range(Cells(5, 2), Cells(lastrow, 2)).AutoFilter Field:=2, Criteria1:= _
        ">=" & CDbl(dataSTART), Operator:=xlAnd, Criteria2:="<=" & CDbl(dataEND)