Macro help with hiding columns based on date ranges in a column

[AlanN]

I have a scheduling spreadsheet where I need to schedule healthcare staff to
cover 6 floors of a longterm care center. In column A I have the floor
number assignment starting with floor 1. In column B I have each day of the
current year from 01/01/2004 to 12/31/2004 repeated for all 6 floors. As you
can imagine it's difficult working with so many rows of data. As the
scheduling person plans in smaller windows of time, I would like to employ a
simple auto-hide of date ranges that they don't need to see for their
planning purpose.
I was hoping that someone could provide me with some macro code to help me
out here:

If I could have a button, that when pressed, would present a small window
prompting the user to enter the start date and an end date that you want to
work with or have access to. The macro would then proceed to hide all rows
in the active sheet where the date that appears in column B is >= start date
and <= end date.
Example data:
Floor Date
1 1/1/2004
1 1/2/2004
1 1/3/2004
1 1/4/2004
1 1/5/2004
1 1/6/2004
1 1/7/2004
1 1/8/2004
1 1/9/2004
2 1/1/2004
2 1/2/2004
2 1/3/2004
2 1/4/2004
2 1/5/2004
2 1/6/2004
2 1/7/2004
2 1/8/2004
2 1/9/2004

If I entered start date = 1/3/2004 and end date =1/5/2005 then I would
expect to see this:

Floor Date
1 1/3/2004
1 1/4/2004
1 1/5/2004
2 1/3/2004
2 1/4/2004
2 1/5/2004


Can anyone help?

Thanks, Alan

 

[Earl Kiosterud]

Alan,

Start with Data - Filter - Autofilter. Use the Custom option. Use "greater
than or =" and select your start date, the "and" option, and "less than or
=" and select your end date.

 

[Çñ־ΰ]

I have a scheduling spreadsheet where I need to schedule healthcare staff to
cover 6 floors of a longterm care center. In column A I have the floor
number assignment starting with floor 1. In column B I have each day of the
current year from 01/01/2004 to 12/31/2004 repeated for all 6 floors. As you
can imagine it's difficult working with so many rows of data. As the
scheduling person plans in smaller windows of time, I would like to employ a
simple auto-hide of date ranges that they don't need to see for their
planning purpose.
I was hoping that someone could provide me with some macro code to help me
out here:

If I could have a button, that when pressed, would present a small window
prompting the user to enter the start date and an end date that you want to
work with or have access to. The macro would then proceed to hide all rows
in the active sheet where the date that appears in column B is >= start date
and <= end date.
Example data:
Floor Date
1 1/1/2004
1 1/2/2004
1 1/3/2004
1 1/4/2004
1 1/5/2004
1 1/6/2004
1 1/7/2004
1 1/8/2004
1 1/9/2004
2 1/1/2004
2 1/2/2004
2 1/3/2004
2 1/4/2004
2 1/5/2004
2 1/6/2004
2 1/7/2004
2 1/8/2004
2 1/9/2004

If I entered start date = 1/3/2004 and end date =1/5/2005 then I would
expect to see this:

Floor Date
1 1/3/2004
1 1/4/2004
1 1/5/2004
2 1/3/2004
2 1/4/2004
2 1/5/2004


Can anyone help?

Thanks, Alan

 

[Alan]

I have tried this approach , but I need to keep the top 12 rows visible to
the user as it has a lot of details relating to the employees like
seniority, vacation days left, etc. That's why I am looking for some macro
code to test the column contents and hide the rows based on the result.

Alan

 

[Earl Kiosterud]

Alan,

Autofilter would be set up starting with the headings (Floor, Date). It
wouldn't include that other stuff (vacation days) that are not part of your
table.

The macros below use an autofilter. The table starts in cell A11-down for
the following.

The following macro accepts start and end dates in cells A9 and B9
respectively, and sets the autofilter for the records in that range of
dates:

Sub FilterDates()
Range("A11").AutoFilter Field:=2, _
Criteria1:=">=" & Range("A9"), _
Operator:=xlAnd, _
Criteria2:="<=" & Range("B9")
End Sub

The user can change the start and/or end dates and run the macro again, or
use the following macro, which resets the filter to show all records in the
table:

Sub AutofilterReset()
Range("A11").AutoFilter Field:=2
End Sub

These macros could be run via buttons on the sheet, or automatically
whenever the start or end dates have been changed via a Worksheet_Change
event macro.

 

[AlanN]

Thanks Earl, I can work with that.
Alan

Alan,

Autofilter would be set up starting with the headings (Floor, Date). It
wouldn't include that other stuff (vacation days) that are not part of your
table.

The macros below use an autofilter. The table starts in cell A11-down for
the following.

The following macro accepts start and end dates in cells A9 and B9
respectively, and sets the autofilter for the records in that range of
dates:

Sub FilterDates()
Range("A11").AutoFilter Field:=2, _
Criteria1:=">=" & Range("A9"), _
Operator:=xlAnd, _
Criteria2:="<=" & Range("B9")
End Sub

The user can change the start and/or end dates and run the macro again, or
use the following macro, which resets the filter to show all records in the
table:

Sub AutofilterReset()
Range("A11").AutoFilter Field:=2
End Sub

These macros could be run via buttons on the sheet, or automatically
whenever the start or end dates have been changed via a Worksheet_Change
event macro.