Search code examples
vbaexcelexcel-2013

Filter between dates in Excel VBA

I have a column in Excel VBA containing lots of dates of the format "dd.mm.yyyy hh:mm" I want to filter this column only between the Date (not by hours). Since it does not filter right using >= and <= (i guess because of the times) i wanted to try to filter the following way:

= StartDate and < EndDate+1

The StartDate & EndDate is entered in a Textbox in the format mm/dd/yyyy by the user

and the way i tried to filter is:

 EndDate_1 = DateAdd("d", 1, EndDate)
 EndDate_1 = Format(EndDate_1, "mm/dd/yyyy")

 Selection.AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<" & EndDate_1

But this does not work (all the Data is filtered out, no error message)

If I just enter 

  Selection.AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

without any formatting on StartDate and EndDate the filtering works (not correctly but at least it works :))

I tried so many thinks but I cant get it running! Thank you for helping!

Solution

  • "<=" & EndDate is the same as "<=" & EndDate & " 00:00:00"

    So try this:

    Selection.AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate & " 23:59:59"