Macro to filter on most current date.

  • Thread starter Travis Patterson
  • Start date
T

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?
 
D

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
 
T

Travis Patterson

Don,

Right on the money.

Much thanks

Regards,
Travis

I entered that code into my ma
 
D

Don Guillett

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top