Excel VBA custom autofilter not finding records

I have a bit of a strange issue which I can't seem to solve.

I have a macro which drives an autofilter from 4 possible filter criteria. 3 of these are a direct population of a variable into the appropriate filter field and work fine, but 1 results in a custom filter and doesn't return any records.

When I look at the filtered sheet I can see that the filter field is active, and when I click on the filter arrow it shows that the custom criteria has been populated with the correct variables, yet the sheet shows 0 records. However, when I click 'OK' in the custom filter popup box, it suddenly works correctly and returns the records which I know are there. ???

I've included an extract of the code. Basically it is intended to check a cell to see if the user has selected a Month or Quarter from the drop down menu. On the sheet this drives a lookup to identify the start and end date for the month/quarter, and the code uses the results of the lookup to set the custom filter to include only those records which are between the start and end of the chosen period. If the user hasn't selected a filter for the period then it is completely ignored.

Thanks for your help Experts, I'm stumped on this one.

Let period_filter = Sheets("Reports").Range("d12").Value
If period_filter <> "" Then
    Let period_start = Sheets("Reports").Range("d13").Value
    Let period_end = Sheets("Reports").Range("e13").Value
    Selection.AutoFilter Field:=6, Criteria1:=">=" & period_start, Operator:=xlAnd _
    , Criteria2:="<=" & period_end
Else
End If

Open in new window

pendulumAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
Try:
Let period_filter = Sheets("Reports").Range("d12").Value
If period_filter <> "" Then
    Let period_start = Sheets("Reports").Range("d13").Value
    Let period_end = Sheets("Reports").Range("e13").Value
    Selection.AutoFilter Field:=6, Criteria1:=">=" & clng(period_start), Operator:=xlAnd _
    , Criteria2:="<=" & clng(period_end)
Else
End If

Open in new window

0
 
pendulumAuthor Commented:
Awesome, thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.