(Solved) – VBA Autofilter – filter out everything that is older than today

  • by

I hava some future Data, and I need to filter out this data as soon as it becmoes outdated based on yesterday’s date. I used macro record to see ho to filter out by date, and I used the code to make a small script.

The problem is that when I run the code from macro myself, all the data is filtering out, instead of the outdated.

     Dim WS_Count As Integer
     Dim I As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count

        ' Insert your code here.
        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
        Set ws = ActiveWorkbook.Worksheets(I)
        A1 = ws.Range("A1").Value
        If (InStr(A1, "HEDGE POSITION") <> 0) And ws.AutoFilterMode = True Then

            Dat = CStr(Date - 1)
            datt = ">" & Dat
            MsgBox datt
            ws.Range("$A$4:$P$40").AutoFilter Field:=12, Criteria1:= _
            datt, Operator:=xlAnd
        End If

     Next I

So this is the code recorded by macro recorder. It doesn’t work when I run it myself (all data vanishes)

ActiveSheet.Range("$A$4:$P$40").AutoFilter Field:=12, Criteria1:= _
    ">16/12/2019", Operator:=xlAnd

Leave a Reply

Your email address will not be published. Required fields are marked *