Filter for all dates two months prior to current month

    Hi, the below code filters last month and all months prior, but I would like to filter for two months prior ie in March I would like Jan and all months prior, cheers.

    Hi,


    You can always use the worksheet function EDate (Arg1, Arg2)


    with Arg1 determined by your reference date

    and Arg2 equal to -2 to adjust date


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Hello again,


    To illustrate Edate with Evaluate ... you can test following macro

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Not sure to exactly understand what you need to filter out ...


    Isn't it ... Between Now and 2 Months prior to Now ... ?


    Do you mind attaching a small sample file ... to illustrate your expected result ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Thanks for the sample file


    Code
    1. Sub Olderthan2Months()
    2. Dim startDate As Long
    3. Dim prevDate As Long
    4. startDate = DateSerial(Year(Now), Month(Now), Day(Now))
    5. prevDate = Evaluate("=Edate(" & startDate & ",-2)")
    6. ' Adjust range to your specific situation '''''''''''''''''''''
    7. Range("A1:A15").AutoFilter Field:=1, Criteria1:="<=" & prevDate
    8. End Sub

    Files

    • Test OZ.xlsm

      (15.27 kB, downloaded 18 times, last: )

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Do you mean like below......I tried and it only gets 1/1/21 instead of all Jan.


    Code
    1. Sub Olderthan2Months()
    2. Dim startDate As Long
    3. Dim prevDate As Long
    4. startDate = DateSerial(Year(Now), Month(Now),1) 'CHANGED THIS....ONLY GETS 1/1/21
    5. prevDate = Evaluate("=Edate(" & startDate & ",-2)")
    6. ' Adjust range to your specific situation '''''''''''''''''''''
    7. Range("A1:A15").AutoFilter Field:=1, Criteria1:="<=" & prevDate
    8. End Sub

    Have a go with following


    Code
    1. Sub Olderthan2Months()
    2. Dim startDate As Long
    3. Dim prevDate As Long
    4. startDate = DateSerial(Year(Now), Month(Now) + 1, 1)
    5. prevDate = Evaluate("=Edate(" & startDate & ",-2)")
    6. ' Adjust range to your specific situation '''''''''''''''''''''
    7. Range("A1:A15").AutoFilter Field:=1, Criteria1:="<=" & prevDate
    8. End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)