Autofilter on date macro not behaving as I think it should.

JonathanA

Board Regular
Joined
Jan 2, 2008
Messages
62
Hi there,

This has really foxed me, I have part of a macro that filters a table in Excel 2007, to only show the relevant data for that week, last week it was working fine but now it has decided to start showing 2 weeks of data even though the filter specifies just one date. Here is a snippet of my code.

Code:
Sub Macro15()

        Sheets("Report").Select
        
    mydate = Range("B2").Value
    'This is a date in cell formatted as UK date dd/mm/yyyy or 11/03/2011
    
    mydatestring = Format(mydate, "mm/dd/yyyy")
Sheets("Data").Select
        ActiveSheet.ListObjects( _
        "Table_View_Sales_Target_Report").Range. _
        AutoFilter Field:=1
        ActiveSheet.ListObjects( _
        "Table_View_Sales_Target_Report").Range. _
        AutoFilter Field:=2
        ActiveSheet.ListObjects( _
        "Table_View_Sales_Target_Report").Range. _
        AutoFilter Field:=1, Criteria1:="Jon A"
        ActiveSheet.ListObjects( _
        "Table_View_Sales_Target_Report").Range.AutoFilter _
        Field:=2, Operator:=xlFilterValues, Criteria2:=Array(1, mydatestring)
End Sub

This code now leaves the table filtered on 11/03/2011 and 04/03/2011, 11th and 4th March whereas last week it worked fine filtering on 04/03/2011 (it is supposed to be a couple of weeks behind) I could understand the problem if the UK and US dates could be misinterpreted, i.e if there was a date in the data of 03/11/2011. I have checked the serial numbers of the 2 dates in question and they are 40606 (04/03/2011) and 40613 (11/03/2011).
Please can anybody help this has really flummoxed me.
:oops:
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

JonathanA

Board Regular
Joined
Jan 2, 2008
Messages
62
Thanks for that, works perfectly now.
Just very annoying about US dates, problems always cropping up everywhere.
Who on earth thinks that MM/DD/YYYY is more logical or makes more sense than DD/MM/YYYY,:confused: rant over!
Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top