most recent date for auto filter within macro

[ultratch47]

This is my Macro

Range("E1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="1/2/2004"

i want the Criteria for the Auto Filter to always be the most recent date listed in Column E (this will always change)

i was thinking maybe we could use formula to pick out the recent date and list that in a cell, in turn listing the criteria as that cell

any helpful suggestions?

 

[Tom Urtis]

One way among several...modify for format of your column E dates:

Sub Test1()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim myDate As Date
myDate = Application.Max(Columns(5))
Columns(5).AutoFilter Field:=1, Criteria1:="=" & Format(myDate, "mm/dd/yy")
Application.ScreenUpdating = True
End Sub

 

[ultratch47]

from reading the macro, it appears user input is needed, i was hoping to stray away from that...

as this is a very long macro that will do a lot of dirty work by just clicking play.

 

[Tom Urtis]

You obviously did not test the macro, there is no user input required, unless you want someone to click the button for you that the macro is attached to.

 

[ultratch47]

i posted first then tested...
sorry for the assumption

the list in the column ranges down to row 745
after the macro the auto filter displays the column with cells 1:745 as hidden

therefor making the entire sheet appear blank
i'm not sure where the macro is lacking but thinks for the help

 

[ultratch47]

actually its weird, the auto filter DOES work, and the most recent date is "HIGHLIGHTED" however the sheet does not display the results of this auto filter, by dropping the menu and re-highlighting, the auto filer works...

 

[Tom Urtis]

The macro works and it tested fine for me based on your description. Are the dates formatted the same in column E, and did you make sure that the formatting they are in in column E is what the macro has them assumed to be on the Format line? That was what I meant by modifying the macro for date format. We could force the issue by formatting column E as a certain date format but I assumed you would want control over the spreadsheet formatting, and have the macro adhere to that.

 

[ultratch47]

ahh very nice, thank you much

that was the problem...

 

[blu817]

This is great!