Filtering with dates working as intended?

118 views Asked by lackdaz At 15 June 2015 at 08:43

I've been playing around with filtering lately and haven't come across much trouble until the bit that involves filtering dates <= a specific date. I'm making a statement of accounts invoicing code that generates monthly statements before the end of every month, ie 31/01/2015, 28/02/2015 and so on...

I used macro recording to copy this bit of code:

ActiveSheet.ListObjects("TSOA").Range.AutoFilter Field:=3, Criteria1:= _
    "<=31/1/2015", Operator:=xlAnd

And modified accordingly to be used in my table:

.Worksheets("SOA").AutoFilterMode = False
        TSOA.Range.AutoFilter Field:=4, Criteria1:=str3
        TSOA.Range.AutoFilter Field:=8, Criteria1:="Unpaid"
        TSOA.Range.AutoFilter Field:=3, Criteria1:="<=31/1/2015"

which also features 2 other filters that are working just fine. Unfortunately, the last date autofilter filters out everything. More puzzling is upon some inspection of the custom date filters, the filter is in place! And when "refreshed" - ie manually choosing the custom date filter under the right-click drop down option and pressing OK without changing any of the inputed values. The autofilter now works as intended!

For more illustration, I put a stop between the first two filters and the last problematic filter and posted the results:

        .Worksheets("SOA").AutoFilterMode = False
        TSOA.Range.AutoFilter Field:=4, Criteria1:=str3
        TSOA.Range.AutoFilter Field:=8, Criteria1:="Unpaid"

        Stop

        TSOA.Range.AutoFilter Field:=3, Criteria1:="<=31/1/2015", Operator:=xlAnd

        Stop

Stock Data with only first two filters After the third problematic filter Inspecting the custom date filter Pressing OK without changing any values