autofilter using string input

  • Thread starter Thread starter KT
  • Start date Start date
K

KT

The following macro doesn't return any records.

Sub filtering()
Dim StartDate As String
Dim cStartDate As String
Dim cEndDate As String

StartDate = ActiveSheet.Cells(1, 4).Value
cStartDate = ">=" & StartDate
cEndDate = "<=" & ActiveSheet.Cells(1, 4).Value + 6

ActiveSheet.Cells(5, 1).Select
Selection.AutoFilter Field:=8, Criteria1:=cStartDate, _
Operator:=xlAnd, Criteria2:=cEndDate
End Sub

However, when I go manually go to check the autofilter
(custom...) window and then click OK, I get the expected
result of the filter.

It seems the criteria for the filter aren't working very
well programatically. How can I overcome this?

TIA for assistance.
KT
 
Have you tried turning on the macro recorder, setting up the filter, then
stopping the recorder and looking at the code. Maybe it will give you the
needed clue.
 
BTW, the code results in the correct fields being
populated in the Custom AutoFilter dialog box - but for
some reason Excel doesn't filter correctly.
 
Sometimes, if you convert the dates to longs, it helps:

cStartDate = ">=" & StartDate
cEndDate = "<=" & ActiveSheet.Cells(1, 4).Value + 6

cStartDate = ">=" & clng(StartDate)
cEndDate = "<=" & clng(ActiveSheet.Cells(1, 4).Value) + 6

But dates can be problems in autofilters via code.
 
Back
Top