Prev: "Optional" output of a Public Type record
Next: Accessing the call stack in an Excel VBA macro?
From: Travis Patterson on 3 Jun 2010 13:58
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?




From: Don Guillett on 3 Jun 2010 15:40
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
dguillett(a)gmail.com
"Travis Patterson" <TravisPatterson(a)discussions.microsoft.com> wrote in
message news:12D03F05-77E4-4559-B0F7-DB9F1B4E6C4A(a)microsoft.com...
> 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?
>
>
>
>

From: Travis Patterson on 3 Jun 2010 16:38
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
> dguillett(a)gmail.com
> "Travis Patterson" <TravisPatterson(a)discussions.microsoft.com> wrote in
> message news:12D03F05-77E4-4559-B0F7-DB9F1B4E6C4A(a)microsoft.com...
> > 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?
> >
> >
> >
> >
>
> .
>
From: Don Guillett on 3 Jun 2010 18:53
Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Travis Patterson" <TravisPatterson(a)discussions.microsoft.com> wrote in
message news:0C53B2D8-BCEE-462B-BEB8-EAEBD1C165B4(a)microsoft.com...
> 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
>> dguillett(a)gmail.com
>> "Travis Patterson" <TravisPatterson(a)discussions.microsoft.com> wrote in
>> message news:12D03F05-77E4-4559-B0F7-DB9F1B4E6C4A(a)microsoft.com...
>> > 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?
>> >
>> >
>> >
>> >
>>
>> .
>>

From: EZnet on 5 Jun 2010 07:56
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
> dguillett(a)gmail.com
> "Travis Patterson" <TravisPatterson(a)discussions.microsoft.com> wrote in
> message news:12D03F05-77E4-4559-B0F7-DB9F1B4E6C4A(a)microsoft.com...
> > 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?
> >
> >
> >
> >
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: "Optional" output of a Public Type record
Next: Accessing the call stack in an Excel VBA macro?