none
Autofilter using dates RRS feed

  • Question

  • I need to add an autofilter for expiration dates in the next 7 days:

    I have the basic part but don't know how to program the criteria to check if date expires in the next 7 days. The cell it is evaluating is date format and sometimes the date is blank so I don't know if I need to take that into account:

    ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=19, Criteria1:=???????



    MEC

    Wednesday, September 7, 2016 11:12 PM

Answers

  • >>>Yes that is true but the real problem is the cells Ithat have dates in them I want to filter for them if those dates expire within the next seven days, the blank space situation is not the real thing I am trying to solve for.

    According to your description, if I have no misunderstandings on your question, you could refer to below code:

    Worksheets("Sheet1").Range("A1:A6").AutoFilter field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="<" & DateAdd("d", 7, Date)

    The result:

    In addition I have any misunderstandings on your question, please correct me and provide more details about your issue, for example screenshot.

    Thanks for your understanding.

    • Proposed as answer by David_JunFeng Monday, September 19, 2016 5:18 AM
    • Marked as answer by David_JunFeng Monday, September 19, 2016 5:18 AM
    Friday, September 9, 2016 2:25 AM

All replies

  • >>>The cell it is evaluating is date format and sometimes the date is blank so I don't know if I need to take that into account:

    According to your description, you could have the filter show only blanks for the specified Field by using: Criteria1:="="

    For more information, click here to refer about How to use AutoFilter Criteria in Excel VBA

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thursday, September 8, 2016 2:15 AM
  • Yes that is true but the real problem is the cells Ithat have dates in them I want to filter for them if those dates expire within the next seven days, the blank space situation is not the real thing I am trying to solve for.

    MEC

    Thursday, September 8, 2016 11:04 AM
  • >>>Yes that is true but the real problem is the cells Ithat have dates in them I want to filter for them if those dates expire within the next seven days, the blank space situation is not the real thing I am trying to solve for.

    According to your description, if I have no misunderstandings on your question, you could refer to below code:

    Worksheets("Sheet1").Range("A1:A6").AutoFilter field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="<" & DateAdd("d", 7, Date)

    The result:

    In addition I have any misunderstandings on your question, please correct me and provide more details about your issue, for example screenshot.

    Thanks for your understanding.

    • Proposed as answer by David_JunFeng Monday, September 19, 2016 5:18 AM
    • Marked as answer by David_JunFeng Monday, September 19, 2016 5:18 AM
    Friday, September 9, 2016 2:25 AM