none
VBA Code To Use Autofilter To Show Expiring Dates RRS feed

  • Question

  • I am trying to use an autofilter to check column 32 (formatted as date) to see if the date has already passed or is expiring in the next 5 days. I thought I could do date +5 but it is not working, can anyone help?

    Sub Pipeline_Expiring()
    Dim ddate As String
    ddate = Month(Date)
    Dim r As Range
    Dim DateToday As Date
    DateToday = Format(Date, "DD/MM/YYYY")
    Set r = ActiveSheet.Range("$a$9:$ax$500")
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
      ActiveSheet.ShowAllData
    End If
    'r.Select
        Selection.AutoFilter
    ActiveSheet.Range("$a$9:$ax$500").AutoFilter Field:=32, Criteria1:="=" & Date + 5
        Range(Cells(r.Rows.Count + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True
        Range("$a$9:$ax$500").Sort Key1:=Range("E10"), Order1:=xlAscending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        ActiveWindow.ScrollColumn = 32
    End Sub



    MEC

    Wednesday, February 28, 2018 3:55 PM

Answers

  • Sorry, I meant

    ActiveSheet.Range("$a$9:$ax$500").AutoFilter Field:=32, Criteria1:="<=" & CLng(Date + 5)

    The use of CLng is intentional - Excel stores dates internally as numbers.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Thursday, March 1, 2018 2:55 AM
    • Marked as answer by mecerrato Thursday, March 1, 2018 8:40 PM
    Wednesday, February 28, 2018 9:15 PM

All replies

  • Try

    ActiveSheet.Range("$a$9:$ax$500").AutoFilter Field:=32, Criteria1:="=" & CLng(Date + 5)

    By the way, you won't use ddate, DateToday and r in your code.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, February 28, 2018 4:28 PM
  • That did not work, see picture below of the value of CLng(Date + 5), the value is 43164 but the data is in date format so I am not sure if this is the reason your code didn't work. Also will it show me dates in the past?


    MEC

    Wednesday, February 28, 2018 4:54 PM
  • Sorry, I meant

    ActiveSheet.Range("$a$9:$ax$500").AutoFilter Field:=32, Criteria1:="<=" & CLng(Date + 5)

    The use of CLng is intentional - Excel stores dates internally as numbers.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Thursday, March 1, 2018 2:55 AM
    • Marked as answer by mecerrato Thursday, March 1, 2018 8:40 PM
    Wednesday, February 28, 2018 9:15 PM
  • Worked perfectly, thank you!!

    MEC

    Thursday, March 1, 2018 8:40 PM