Macro to filter on most current date.

[Travis Patterson]

OK...so I hope I can explain this correctly

I need macro code that will auto filter by most recent date(not neccesarily
todays date).

The dates are all input in column A (oldest to newest). Basically the code
needs to be able to open the auto filter for column a and pick the date at
the bottom of the list (since the dates are arranged oldest to newest the
most recent date is at the bottom of the auto filter list.)

Does this make sense to any one besides me?



I recorded a macro where I manually opened the auto filter and chose the
most recent date in column A:

ActiveSheet.Range("$A$1:$K$168").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(2, "6/3/2010")

So this code will only filter on 6-3-2010. but as I continue to add more
dates to the database (as time goes on) I need the macro to filter by the
most recent date

is this possible?

 

[Don Guillett]

Sub filterbylastdate()'change column & FORMAT to suit
lr = Cells(Rows.Count, 1).End(xlUp).Row
Rows(6).AutoFilter Field:=1, Criteria1:= _
Format(Cells(lr, 1), "mm/dd/yyyy")
End Sub

 

[Travis Patterson]

Don,

Right on the money.

Much thanks

Regards,
Travis

I entered that code into my ma

 

[Don Guillett]

Glad to help

 

[EZnet]

Please explain the sub so we can understand how does it work.

 

[Don Guillett]

It simply finds the last date and formats it so the autofilter will
understand it.