Results 1 to 2 of 2
  1. #1
    New Lounger
    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.

    Attached Files Attached Files

  2. #2
    New Lounger
    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):

    Option Explicit
    ' 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
    End If
    End If
    Application.EnableEvents = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts