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

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






0
here6701 (24)
6/10/2004 2:37:38 AM
excel 39879 articles. 2 followers. Follow

5 Replies
900 Views

Similar Articles

[PageSpeed] 6

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.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"AlanN" <here@now.com> wrote in message
news:SPPxc.152851$Ar.71319@twister01.bloor.is.net.cable.rogers.com...
> 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
>
>
>
>
>
>


0
nowhere1083 (630)
6/10/2004 3:12:33 AM
"AlanN" <here@now.com> д���ʼ�
news:SPPxc.152851$Ar.71319@twister01.bloor.is.net.cable.rogers.com...
> 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
>
>
>
>
>
>


0
6179888 (1)
6/10/2004 6:30:13 AM
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" <nowhere@nowhere.com> wrote in
news:#G82IjpTEHA.1508@TK2MSFTNGP11.phx.gbl: 

> 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.
> 

0
me3529 (37)
6/10/2004 9:02:49 PM
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.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Alan" <me@here.now> wrote in message
news:Xns9504ADBCDC9F2meherenow@207.46.248.16...
> 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" <nowhere@nowhere.com> wrote in
> news:#G82IjpTEHA.1508@TK2MSFTNGP11.phx.gbl:
>
> > 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.
> >
>


0
nowhere1083 (630)
6/10/2004 10:17:25 PM
Thanks Earl, I can work with that.
Alan
"Earl Kiosterud" <nowhere@nowhere.com> wrote in message
news:O2lx4izTEHA.1984@TK2MSFTNGP12.phx.gbl...
> 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.
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Alan" <me@here.now> wrote in message
> news:Xns9504ADBCDC9F2meherenow@207.46.248.16...
> > 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" <nowhere@nowhere.com> wrote in
> > news:#G82IjpTEHA.1508@TK2MSFTNGP11.phx.gbl:
> >
> > > 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.
> > >
> >
>
>


0
here6701 (24)
6/11/2004 12:22:03 AM
Reply:

Similar Artilces:

help with formulas
hello I am designing a spreadsheet with some formulas to be self counting. I am stock for some certains formulas to creat what I would like to acheive, it is difficult to explaind just by writting. It would be easier if I could send an example of my spreadsheet by an attachment. How can I do that ? Hi don't send an attachment. Just try to explain your question in plain text or post some example rows of your data (also in plain text) -- Regards Frank Kabel Frankfurt, Germany "Jan" <Jan@discussions.microsoft.com> schrieb im Newsbeitrag news:B20CBBD3-F6D4-4AED-B635-DD0...

need help with an excel spreadsheet
I have an excel spreadsheet where questions are answered in one area, and the answers show up on a form in another area of the sheet that gets printed. Instead of printing, how can I either email the print area or have just the print area saved as a file? And, with a macro, can the new file be named automatically by what's in a particular cell? Thanks, Russ You don't say whether the recipient would need to edit the data. If not, there are numerous programs available (free, shareware and commercial) which enable print output to be stored as a PDF file. Another option would be to c...

Create a status designation from 5 date fields using "Not IsNull"
I am still learning, lots that I don't know, but I am trying to create a status designation from 5 date fields using "Not IsNull" and having difficulty. I am not sure if this will fuction best in query, forms... can I do this on the table??? After some research, this is what I have in a query for now and it seems to work but I don't want a number: Status: IIf(Not IsNull([cogradulate]),5,IIf(Not IsNull([verifydate]),4,IIf(Not IsNull([permCAdate]),3,IIf(Not IsNull([shorttermCAdate]),2,IIf(Not IsNull([containmentdate]),1,0))))) When I change it to a word in...

Macro warning with no macros
I have a workbook that when I try to open it I get the warning tellin me it contains macros and asking whether I want to Enable or Disabl them. I put no macros, no VB scripts, nothing at all in the workboo other than standard Excel formulas and some charts. I developed it i Excel 2002, SP-2, but have moved it back and forth between tw computers, the other which has Excel 97. Regardless of whether Enable or Disable or change the security settings so as not to get th warning, once the workbook is open even if I go t Tools-Macro-Macros... it lists no macros. How can I find out what's cau...

Conditional Formula
Hello, I would like some help in evaluating the value of a cell that falls within a certain range. For example: If A2 is less than 1 and greater than 10 "TRUE" Your help is appreciated. Ed Hi Eduardo do you mean if A2 is less than 1 OR greater than 10??? if so format / conditional formatting formula is =OR($A2<1,$A2>10) or if you mean greater than 1 and less than 10 =AND($B1>1,$B1<10) Cheers JulieD "Eduardo" <Eduardo@discussions.microsoft.com> wrote in message news:00BD63BC-81A8-41B9-86F0-BAE57246B86C@microsoft.com... > Hello, > I would li...

Accessing a 97 database with Access 2000- HELP!
Here is my situation. I have a database that was created in Access 97. It has never been converted yet over to Access 2000. When I try to open the database, I get the usual message of "Do I want to convert the database or just open it" I choose open the database. As soon as I choose this, an error message comes up that says Record is Deleted. I found one article on the Help site that deals with this message but it refers to a networked Access 2000 database. It talks about using the Repair function. I did try the repair function on the 97 version but it did not get rid of th...

Macro
Can anybody tell me how to kick off a macro automatically as file is opened? Regards ...

Macro
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: intel Hi, I'm thinking of buying Mac: Office 2008, but before I do I want to check that I will be able to download Scritpsmart collection of templates without any glitches: <http://www.bbc.co.uk/writersroom/scriptsmart/faq.shtml> Does anyone know if I can do this? Regards, SP. In article <ee98ff5.-1@webcrossing.caR9absDaxw>, StregaItalia@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (Tiger) > Processor: intel > > Hi, > > I'm thinking of buying Mac: Of...

HELP !!
I have a long long column of company names ie Company a Company b Company c But some of them are duplicated ie Company a Company b Company b Company b Company c Company c Company c How can I do a count on these companies so that I'm only provided with a count of each individual company name. (rather than the duplicates as well). So the count for the list of duplicates above would be '3'. Any one help???? Regards Dave =SUM(1/COUNTIF(A1:A7,A1:A7)) This formula must be array entered - You must press ctrl+shift+enter (as opposed to just hitting enter) after inserting the...

Terminal Server based Outlook 2007 printing hieroglyphics
We are running terminal services(Server 2008 R2) with Outlook 2007 installed (SP2). No other Office components are installed. When printing emails, (all other printing is fine), the text becomes compressed and looks like hieroglyphics. 1. I have tried removing all printer drivers and re-installing the drivers with no luck. 2. I have also applied the lastest patches and Service packs to the server and Outlook. 3. I have tried using different types of drivers with each printer ie. PS, MS, PCL5e, PCL6 etc with no luck. Any help would be appreciated as users are getting very fr...

Run macro automatically.
How do I make a macro run automatically when a worksheet it is attached to is loaded? right click on the sheet tab>view code>left window worksheet>right window activate -- Don Guillett SalesAid Software donaldb@281.com "Excel macro" <Excel macro@discussions.microsoft.com> wrote in message news:DD7AF1E3-9263-4523-AC49-A43ABA1AB9D0@microsoft.com... > How do I make a macro run automatically when a worksheet it is attached to is > loaded? I am unable to find "activate " when I right click on worksheet. ( i assume that you are referring to the windo...

Help with macro/and or formula?
Perhaps this is a silly question, but i just can't figure out how t format this worksheet. The problem is that a lot of the cells hav numbers in them with negative signs in the back instead of in the fron (ie. 23-) and i was wondering if anyone knew of a macro/formula i coul use to quickly fix this problem for all the cells. I would b extremely grateful. Thanks -- brefed1 ----------------------------------------------------------------------- brefed15's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3557 View this thread: http://www.excelforum.com/showthre...

Changing Rows to Columns
Is it possible to easily rotate a spreadsheet or part of a spreadshet, to change the data in rows to columns? Do to a sorting problem in the charts I create, it seems I have to sort the data in the spreadsheet first. Any help would be appreciated. Hi, Sure is a way. select & copy your data. then select the cell where you want to re-paste; right-click and select paste special; in the dialog box check "transpose". Ok. jeff >-----Original Message----- >Is it possible to easily rotate a spreadsheet or part of a >spreadshet, to change the data in rows to columns? D...

Attached Toolbars Macros
Excel 2003 I have a custom tool bar, this tool bar has several items, each item is pointing to a VBA code. This tool bar is attached to the worksheet. I've copied this spreadsheet to several computers. After I copied the spreadsheet I decided to rename the spreadsheet, after I renamed the spreadsheet the Toolbar Item's macros were still pointing to the old name, I reasigned each tool bar item to the current spreadsheet, saved the spreadsheet and everything was working on my computer, but when I re-copied the new spreadsheet to other computers and I clicked on the Toolbar Item...

Moving rows to columns #2
Hello group, I have an Excel sheet from which I need to automaticaly generate a new worktab (is that the correct translation to English?) which does some format editing. In the current sheet I have the data lay-out as follow: X A1 A2 A3 A4 A5 X B1 B2 B3 B4 B5 etc. Y C1 C2 C3 C4 C5 Y D1 D2 D3 D4 D5 etc. I need to move that to: X A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 etc. Y C1 C2 C3 C4 C5 D1 D2 D3 D4 D5 etc. Is there any way to do this? I need to keep the old format for human editing, the sheet with the new format should be generated automaticaly (perhaps using a macro?)? Please help! Thank y...

Help with an update
Somebody can tell me why i cant installe update Microsoft .NET Framework 1.1 Service Pack 1 Security Update for Windows 2000, Windows XP, Windows Vista, Windows Server 2008, Windows 7, and Windows Server 2008 R2 (KB953297) I tried to installe update many times but dont work . What i can do...can help me? Thank you. Copied announcement from MowGreen To: *All Windows Users* who encounter installation issues when installing Security updates for .NET Frameworks Every time there's a Security update for .NET Frameworks there is an inordinate amount of posts dealing...

Hiding negative numbers
I know how to format cells and customize under the number tab, but is there a way for when a formula returns a negative number for it to have the cell be blank or equal zero? Thank you. One way: Format/Cells/Number/Custom General;;0;@ XL display formats have 4 fields. By default the first is for positive numbers, the second for negative numbers, the third for zero and the fourth for text. By leaving the negative number format blank, XL will not display negative numbers. In article <1535501c3fa4c$eccf8f70$a401280a@phx.gbl>, "Jay" <anonymous@discussions.microsof...

Protected sheets and running macros
i have a sheet with a macro ran by a normal button at the top. Howeve when i protect the worksheet where ever i click on the sheet the macr executes. Can any one offer any advice on how to stop this? Thanks Robert -- Message posted from http://www.ExcelForum.com hi, i am not able to reproduce the problem, hence i may not be able t provide a panacea for that issue. here is what i suggest. i have a sheet that has a macro that is run when a control toolbo button (that is, not a form button) is clicked. this sheet is protected based on the user logged in. what i have don is to protect t...

Lists
Okay I am new to excell bar drawing little spreadsheets to do my own financing. I am currently setting up a sheet and need the users to be able to select from a list of options only and not input thier own text - does that make sense? also i ahve 12 names on the list and one master with all on - would like the data entered on to a names list to be automatically entered onto the master or vice versa which ever is easiest. sorry if this sounds jumbled but i am totally lost and this has to look really hot for work! regards Kelly - England Hi Kerry, don't know a great deal on forms ...

Macro Freezes Reading Pane
I have a macro that Sends/Receives Tasks from Project Server into Outlook Tasks. Everything is working fine, except one weird thing that happens. I have the Timer set on the macro and it starts as soon as Outlook starts. The macro gets the tasks from ProjectServer every hour. As soon as the macro starts running the Reading pane freezes in the Inbox. If i stop the macro then Reading pane is back to normal. Can anyone please tell me if there is something I can do in VBA to fix this annoying issue. Thanks a lot ...

Macro to remove rows?
Hi, I receive thousands of rows of data from an extract that comes from a database. The data extract automatically generates page headers that need to be removed, and I would love to automate this process. The headers do have a pattern to them, but I'm not clever enough to figure out a good way to use that to write a macro. The first row of the header starts with hyphens ------------------------------- Then there's a variable number of rows of header data to delete followed by another row of hyphens ------------------------------- Then 2 additional rows that must be deleted. The nu...

Tracking Sheet help.
Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the amount of time's that I've made an entry of time on sheet 2. The times I enter on sheet 2 are used to track vehicles that are passing a set location. On sheet 2 I enter the times in column D. The vehicle's I count have numbers to ID them in column F. The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of the number I need to associate with my count is the first four digits, 401N, 401S. Is it possible to do this? Yep, use a sumproduct function to count using the left 4 digits as...

Calling on a macro within a macro!
Hey All!! Quick Question - How do you call up a macro within a current macro? I have one big macro and I need it to jump to several other macros when it runs! Does it matter where the other macros are stored or is it better to have them all in one module? Help appreciated Thnx! Hi Mellowe, '=============>> Public Sub Main() 'Your code Call One 'your code End Sub '<<============= '=============>> Public Sub One() MsgBox "Hi from One" End Sub '<<============= > Does it matter where the other macros are stored or ...

Please Help Me Secure My Form Results!!!
I have a FP 2003 form on a SSL page that contains confidential information. I would like to send it to a password protected page that my other employees can access with a unique username/password and then print the form results. I currently send it to the _private folder in formatted text– but I am the only one who can access it-- through FP. I tried to setup a subsite – but the Browse button in FrontPage Form Results does not see the subsite. I have tried other forums and paid for advice from “experts” on other sites – but no one seems to be able to tell me exactly how this ...

shortcut keys to macros
Hi, Is it at all possible to assign shortcut keys to macros in Outlook? Thx, Hans ...