Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Put current date into a cell when a macro is run | Excel Programming | |||
save workbook as current date using a macro | Excel Discussion (Misc queries) | |||
How do you set cursor position at current date in macro? | Excel Worksheet Functions | |||
A macro for the today's date...not the current date | Excel Programming |