Results 1 to 7 of 7

Thread: autofilter of range problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2012
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default autofilter of range problem

    Hi,

    Wondering if anyone can help with this....

    I'm trying to make my code set up two filters on my sheet - the first is a date on column 39 (based on pre defined date dat minus 28days) and the second is to filter for containing not blank cells.

    Code:
    With Sheets("OPS PLANNER")
                .AutoFilterMode = False
                    With .Range("A9:AT5010")
                         .AutoFilter
                         .AutoFilter Field:=39, Criteria1:="<=" & dat - 28
                         
                         .AutoFilter Field:=45, Criteria1:="<>"
                         
                    End With
    
    Exit Sub
    It seems to run ok but for some reason is coming up with nothing even when there are results.

    Probably something obvious! but can anyone help?

    Thanks
    Tom

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,767
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: autofilter of range problem

    Your second criteria translates to "doesn't equal". What are you trying to get it to do because "doesn't equal" doesn't equate.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,426
    Post Thanks / Like
    Mentioned
    79 Post(s)
    Tagged
    16 Thread(s)

    Default Re: autofilter of range problem

    Quote Originally Posted by alansidman View Post
    Your second criteria translates to "doesn't equal". What are you trying to get it to do because "doesn't equal" doesn't equate.
    Criteria1:="<>"
    equates to "is not equal to blank", which is what the OP stated they want. If you record a macro to auto filter (Text Filter... -> Does not equal -> leave the top right box empty -> OK) the criteria is recorded as above.

    Tom
    If you change this line in your code does it help?
    Code:
    .AutoFilter Field:=39, Criteria1:="<=" & CLng(dat - 28)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Jan 2012
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: autofilter of range problem

    Morning, yep - that seemed to be what the problem was.

    I have changed it to be 28 days older than today and it seems to work

    Code:
    dat = ">=" &  date - 28
    
    With Sheets("OPS PLANNER")
                .AutoFilterMode = False
                    With .Range("A9:AT5010")
                         .AutoFilter
                         .AutoFilter Field:=39, Criteria1:=dat
                         
                         .AutoFilter Field:=45, Criteria1:="<>"
                         
                    End With
    Thanks
    Tom

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: autofilter of range problem

    Peter, just seen your reply - and thanks for your help.

    I think I've kind of done a similar thing to you suggest?


    Thanks
    Tom

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,426
    Post Thanks / Like
    Mentioned
    79 Post(s)
    Tagged
    16 Thread(s)

    Default Re: autofilter of range problem

    Quote Originally Posted by tomleitch View Post
    Peter, just seen your reply - and thanks for your help.

    I think I've kind of done a similar thing to you suggest?


    Thanks
    Tom
    Your change doesn't work for me as I understand what you are trying to do, but if it is working for you, that's all that matters.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Jan 2012
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: autofilter of range problem

    Err.... it seems to be working for me!

    THanks again,
    T

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •