Results 1 to 2 of 2
Thread: Macro to auto-hide rows
2012-10-18, 06:14 #1
- Join Date
- Feb 2007
- Thanked 0 Times in 0 Posts
Macro to auto-hide rows
I'm a doctor trying to set up an excel spreadsheet which is meant to serve two purposes.
1) Keep a list of current inpatients under my care
2) Help facilitate research
What that means on practical terms is that on a day to day basis I only want it to show the patients who are currently admitted but I want to be able to pull up discharged patients when I need to.
It occured to me it might be possible to have a macro which automatically hides a patient once a discharge date has been filled in.
Would anyone be able to help me with this?
I've attached my current draft of the spreadsheet.
Each admission/patient occupies 1 row. At present the discharge date occupies column BC (although this may change so if someone could also tell me how to adjust the macro if it does then that would be very helpful).
Any help very much appreciated.
2012-10-18, 08:40 #2
- Join Date
- Dec 2009
- Goulburn. NSW, Australia
- Thanked 4 Times in 4 Posts
Right-click the "Sheet1" tab, and select "View Code".
In the VBA code screen, paste the following code (if you change the column for discharge date, change the number shown):
' Hide a row if a patient is discharged.
Private Sub Worksheet_Change(ByVal Target As Range)
Const cdblDischargeCol As Double = 55 ' Column BC is the 55th column
Application.EnableEvents = False
If (Target.Column = cdblDischargeCol) Then
If (Target.Text <> vbNullString) Then
Target.EntireRow.Hidden = True
Application.EnableEvents = True