Filtering a Date Range

[Leslie P via OfficeKB.com]

I have a spreadsheet that lists, with start dates in column M and end dates
in column P. I would like to be able to automatically filter the rows if
today's date falls inbetween the start and end dates. Can anyone clue me in
as to how to do this?

Thanks,
Leslie

 

[Earl Kiosterud]

Leslie,

You can use an Autofilter. In column M, use Custom, "less than" (or "less
than or equal to"), and use today's date. In column P, use "greater than"
and today's date. You say you want this automatic. In that case, you'll
need to record this in a macro, and have it pick up today's date. The code
might look like

Sub SetFilter()
Range("M1").AutoFilter Field:=1, Criteria1:="<=" & Now(),
Operator:=xlAnd
Range("M1").AutoFilter Field:=4, Criteria1:=">=" & Now(),
Operator:=xlAnd
End Sub

To reset the filter (show all), use this:

Sub ResetFilter()
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=4
End sub

You'd need only paste this from here into a module, then make buttons (or
something), and assign them to the macro (right click one, Assign macro).

It would be a good idea to put the date on the sheet if you'll be printing
this. The macro could do that too.

 

[Leslie P via OfficeKB.com]

Thanks for your help, but when I tried it out, it ended up deleting all rows..
.. Is there a specific format that my dates need to be in to go along with
the Now() function?

Leslie,

You can use an Autofilter. In column M, use Custom, "less than" (or "less
than or equal to"), and use today's date. In column P, use "greater than"
and today's date. You say you want this automatic. In that case, you'll
need to record this in a macro, and have it pick up today's date. The code
might look like

Sub SetFilter()
Range("M1").AutoFilter Field:=1, Criteria1:="<=" & Now(),
Operator:=xlAnd
Range("M1").AutoFilter Field:=4, Criteria1:=">=" & Now(),
Operator:=xlAnd
End Sub

To reset the filter (show all), use this:

Sub ResetFilter()
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=4
End sub

You'd need only paste this from here into a module, then make buttons (or
something), and assign them to the macro (right click one, Assign macro).

It would be a good idea to put the date on the sheet if you'll be printing
this. The macro could do that too.
--
Earl Kiosterud
www.smokeylake.com

I have a spreadsheet that lists, with start dates in column M and end dates
in column P. I would like to be able to automatically filter the rows if

[quoted text clipped - 4 lines]

Thanks,
Leslie

 

[Dave Peterson]

Dates don't always play nice with autofilters. Sometimes, this'll work:

Sub SetFilter()
Range("M1").AutoFilter Field:=1, Criteria1:="<=" & clng(date)
Range("M1").AutoFilter Field:=4, Criteria1:=">=" & clng(date)
End Sub

Thanks for your help, but when I tried it out, it ended up deleting all rows..
. Is there a specific format that my dates need to be in to go along with
the Now() function?

Leslie,

You can use an Autofilter. In column M, use Custom, "less than" (or "less
than or equal to"), and use today's date. In column P, use "greater than"
and today's date. You say you want this automatic. In that case, you'll
need to record this in a macro, and have it pick up today's date. The code
might look like

Sub SetFilter()
Range("M1").AutoFilter Field:=1, Criteria1:="<=" & Now(),
Operator:=xlAnd
Range("M1").AutoFilter Field:=4, Criteria1:=">=" & Now(),
Operator:=xlAnd
End Sub

To reset the filter (show all), use this:

Sub ResetFilter()
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=4
End sub

You'd need only paste this from here into a module, then make buttons (or
something), and assign them to the macro (right click one, Assign macro).

It would be a good idea to put the date on the sheet if you'll be printing
this. The macro could do that too.
--
Earl Kiosterud
www.smokeylake.com

I have a spreadsheet that lists, with start dates in column M and end dates
in column P. I would like to be able to automatically filter the rows if

[quoted text clipped - 4 lines]

Thanks,
Leslie

 

[Leslie P via OfficeKB.com]

I got it to work, I had to change the fields to 13 and 16, my excel won't
except it any other way. Thanks for all your help!!!

Leslie

Dates don't always play nice with autofilters. Sometimes, this'll work:

Sub SetFilter()
Range("M1").AutoFilter Field:=1, Criteria1:="<=" & clng(date)
Range("M1").AutoFilter Field:=4, Criteria1:=">=" & clng(date)
End Sub

Thanks for your help, but when I tried it out, it ended up deleting all rows..
. Is there a specific format that my dates need to be in to go along with

[quoted text clipped - 36 lines]

 

[Dave Peterson]

Ah, those field numbers start with the first column that's in the filtered
range.

So if you applied filters to A1:X999, then M would be field 13.

I got it to work, I had to change the fields to 13 and 16, my excel won't
except it any other way. Thanks for all your help!!!

Leslie

Dates don't always play nice with autofilters. Sometimes, this'll work:

Sub SetFilter()
Range("M1").AutoFilter Field:=1, Criteria1:="<=" & clng(date)
Range("M1").AutoFilter Field:=4, Criteria1:=">=" & clng(date)
End Sub

Thanks for your help, but when I tried it out, it ended up deleting all rows..
. Is there a specific format that my dates need to be in to go along with

[quoted text clipped - 36 lines]

Thanks,
Leslie