Filtering a Date Range
 

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

Filtering a Date Range
 

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

"Leslie P via OfficeKB.com" wrote in message
...
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

Filtering a Date Range
 

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?

Earl Kiosterud wrote:
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


--
Message posted via http://www.officekb.com

Filtering a Date Range
 

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



"Leslie P via OfficeKB.com" wrote:

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?

Earl Kiosterud wrote:
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

--
Message posted via http://www.officekb.com

--

Dave Peterson

Filtering a Date Range
 

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

Dave Peterson wrote:
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



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1

Filtering a Date Range
 

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.

"Leslie P via OfficeKB.com" wrote:

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

Dave Peterson wrote:
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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1

--

Dave Peterson