Filter Column A Dates with most recent month

[Chandrakant]

Hi,

Good Morning, Need a help with macro, column A which has multiple dates I want to filter the column with most recent month i.e. the most recent month in the dates is March-2025 so after clicking the button it should filter all March-2025 dates.

I have a macro and it finds a most recent month but does not filter accordingly.

Thanks in advance.

 

[p45cal]

    ws.Range("A1:A" & lastRow).AutoFilter Field:=1, _
        Criteria1:=">=" & CLng(DateSerial(recentYear, recentMonth, 1)), _
        Criteria2:="<" & CLng(DateSerial(recentYear, recentMonth + 1, 1))

 

[vletm]

With 'one date'

Sub Do_It()
    With ActiveSheet
        d = WorksheetFunction.Max(.Range("A:A"))
        .Range("$A$1:$A$368").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(1, Format(d, "m/d/yyyy"))
    End With
End Sub

 

[Marc L]

Hello, according to the attachment the VBA procedure revamped :​

Sub FilterMostRecentMonth()
   With [A1].CurrentRegion
        V = Application.Max(.Columns(1))
        If IsError(V) Then Beep Else .AutoFilter 1, ">" & V - Day(V)
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[vletm]

Or as below ... without 'dates'

Sub Do_It()
    Range("A:A").AutoFilter 1, 8, 11
End Sub

 

[Marc L]

Or as below ... without 'dates'

That works only for 'last month' but fails with the attachment as it is if we are in may or march …​

​

 

[vletm]

As asked I want to filter the column with most recent month
... the most recent months data is in that file ... March-2025.

 

[Marc L]

Do your post #5 way still work if we are in 2026 with the attachment as it is ?‼​

As clearly exposed in the initial post « the most recent month in the dates »​

meaning the most recent month from the data so very not according to the current month,​

the reason why Max worksheet function is the easy way to go …​

​

 

[Chandrakant]

With 'one date'

Sub Do_It()
    With ActiveSheet
        d = WorksheetFunction.Max(.Range("A:A"))
        .Range("$A$1:$A$368").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(1, Format(d, "m/d/yyyy"))
    End With
End Sub

Thank you very much vletm it worked.

 

[Chandrakant]

Hello, according to the attachment the VBA procedure revamped :​

Sub FilterMostRecentMonth()
   With [A1].CurrentRegion
        V = Application.Max(.Columns(1))
        If IsError(V) Then Beep Else .AutoFilter 1, ">" & V - Day(V)
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Thank you very much Marc L it worked.

 

[Chandrakant]

Or as below ... without 'dates'

Sub Do_It()
    Range("A:A").AutoFilter 1, 8, 11
End Sub

This works only if the month is March 2025

 

[vletm]

Chandrakant​

... hmm? Are You sure over 99%?
It should Filter all values related to last month.
Same way could solve with this rough sample some other cases.
... Do selection from cell D1

 

[Chandrakant]

Chandrakant​

... hmm? Are You sure over 99%?
It should Filter all values related to last month.
Same way could solve with this rough sample some other cases.
... Do selection from cell D1

Thank you very much for you precious time, but what I want is to filter which ever is the most recent month in the column and it is been achieved with your earlier Sub Do_It

 

[vletm]

Chandrakant​

Read in one way
... the most recent month in the A-column (Your file) is Oct-2023 (cell A368).
Reply #12 was a rough sample with Excel used terms.
My the 1st sample works only, if there are dates max 31-March-2025.
But if You will add later dates (eg 01-Apr-2025) there, then ... no!
You'll get Apr-2025 data ... and so on.
My other two samples would show Mar-2025-data (the latest sample with correct selection), if You'll run those in this month (Apr-2025).
If You'll run those next month (May-2025) then it will show Apr-2025- data.

 

[S P P]

Chandrakant

Use it like this and see if it helps

 

[Marc L]

to filter which ever is the most recent month in the column

The reason why Max worksheet function is the easy way to go if no error within the column.​

In case of an error Max fails then if it's common replace Max with a loop like in your attachment …​

​