Autofilter date range from another cell

    Hello everyone.


    Been scratching my head with this one. Any help would be absolutely fantastic, a godsend.


    So I have a table. I'm looking to extract data after applying various filters.


    One of them is the date. I have the date in another cell, and it's in a format of mm/dd/yy.


    Here is the code...


    Code
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _
        ">=worksheets(1).AA16", Operator:=xlAnd, Criteria2:="05/26/17"


    For whatever reason, it's not filtering the thing. It doesn't give me an error message to say it's wrong either.


    Can anyone tell me whether this is possible?


    Many thanks


    PS , I also tried the following, and that didn't work either..


    Code
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _
        ">=TODAY()-WEEKDAY(TODAY()-7,16)", Operator:=xlAnd, Criteria2:="05/26/17"

    Re: Autofilter date range from another cell


    Did you try...

    Code
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _ 
">=" & worksheets(1).AA16, Operator:=xlAnd, Criteria2:="05/26/17"


    Also, the "format" of the date shouldn't make a difference... just make sure that AA16 is a valid date that Excel understands (you should be able to right click on the cell AA16, and format as "number" to get the decimal representation of the date)


    If the above doesnt work You might need to coerce the date to a "Long" value... do this as follows:

    Code
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _ 
">=" & clng(cdate(worksheets(1).AA16)), Operator:=xlAnd, Criteria2:=clng(cdate("05/26/17"))


    HTh
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Autofilter date range from another cell


    Thanks for your help.


    Unfortunately none of the above lines of code worked. "Object doesn't support this property or method".


    :(

    Re: Autofilter date range from another cell


    Thats odd... let me look closer. I hand typed those without testing, so wasnt sure if I got it exactly right. Not sure why it would throw that specific error though. Can you attach a sample workbooK with confidential data removed?

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Autofilter date range from another cell


    Sorry, sometimes its the obvious things that catch us out...


    This worked for me just now on my own sample.


    Code
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:=">=" & Worksheets(1).Range("AA16"), Operator:=xlAnd, Criteria2:="05/26/17"


    I wasnt forming the worksheet range correctly.
    You had Worksheets(1).AA16 it should be Worksheets(1).Range("AA16")


    Regards
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Autofilter date range from another cell


    Lovely, that worked amazing for the first one.


    However if I change the second critera, i get a blank result again.


    Code
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _
    ">=" & Worksheets(1).Range("AB14"), Operator:=xlAnd, Criteria2:=Worksheets(1).Range("AB16")



    All I did was copy and paste the code and change the cell number.


    Really struggling to get my head around vba :dead:

    Re: Autofilter date range from another cell


    Dont panic - youre doing great! :)


    Code
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _ 
">=" & Worksheets(1).Range("AB14"), Operator:=xlAnd, Criteria2:=Worksheets(1).Range("AB16")


    What you are doing In simple English - if there is such a thing - is Filter column 3 of my table K20 to Q58, where that column has dates greater than or Equal the date value in cell AB14.... AND where the dates in that column are EXACTLY equal to the date value in AB16.


    So my guess the date in AB16 doesnt exist in the 3 column.... OR... the date in AB16 is not a valid date that Excel recognises...


    Anyway, cut a long story short... I believe, what you want to do was:


    Code
    ActiveSheet.Range("$K$20:$Q$58").AutoFilter Field:=3, Criteria1:= _ 
">=" & Worksheets(1).Range("AB14"), Operator:=xlAnd, Criteria2:="<"&Worksheets(1).Range("AB16")


    What you are doing In simple English - is Filter column 3 of my table K20 to Q58, where that column has dates greater than or Equal the date value in cell AB14.... AND where the dates in that column are less than (i.e. before) the date value in AB16. i.e. you are now filtering between two dates...


    Stick with the autofilter... its one of the most powerful "Search" features that Excel has to offer and too many people overlook it. Filtering for dates can be a fickle business... but you get used to its oddities after a while.


    Keep posting back and we'll keep helping.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login