Macro to filter on most current date.
 

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?

Macro to filter on most current date.
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Travis Patterson" wrote in
message ...
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?

Macro to filter on most current date.
 

Don,

Right on the money.

Much thanks

Regards,
Travis

I entered that code into my ma

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Travis Patterson" wrote in
message ...
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?





.

Macro to filter on most current date.
 

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Travis Patterson" wrote in
message ...
Don,

Right on the money.

Much thanks

Regards,
Travis

I entered that code into my ma

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Travis Patterson" wrote in
message ...
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?





.

Macro to filter on most current date.
 

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

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Travis Patterson" wrote in
message ...
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?





.

Macro to filter on most current date.
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EZnet" wrote in message
...
Please explain the sub so we can understand how does it work.

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Travis Patterson" wrote in
message ...
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?





.