Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Whistler, Br. Columbia, Canada
    Thanked 0 Times in 0 Posts

    auto-hide the same rows over many sheets

    I have a spreadsheet with many sheets where I want to hide the same rows on all but the first 3 sheets. Sheet 1 is the summary page - on this sheet I enter a date range (also have a pair of cells that convert that date range to row numbers). On sheets 4 to end (the sheet names that should be affected can be determined from the summary page if necessary), I want to hide the rows that don't fall within the date range. For ease of coding and speed of execution it would be fine to only hide the rows that fall before the beginning of the date range. All sheets will have the same rows hidden. I'd like to have the hiding happen on a change in the date range. Expanding the range would have to unhide previously hidden rows.

    How do I do this??

    I found this code:
    Sub HURows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 3

    For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value < 5
    Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
    Next RowCnt

    which seems like a good place to start but I'm not sure how to go from here...

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 229 Times in 210 Posts
    Not a lot to go on there, but basically something like:
    Sub HURows() 
    DIm n as long, BeginRow as Long, EndRow as Long
    With Sheets("Summary")
    ' change cells as required
    BeginRow = .range("B1").value
    EndRow = .range("B2").value
    End with 
    ' Loop through sheets 4 to last
    For n = 4 to Sheets.Count
       With Sheets(n)
          ' Unhide all rows
          .Usedrange.Entirerow.Hidden = False
          ' Hide all rows before BeginRow (assuming it's not 1)
          If BeginRow > 1 then .Rows(1).Resize(BeginRow - 1).Hidden = True
          ' Hide all rows after EndRow
          If EndRow < .Rows.Count Then .Rows(EndRow + 1).Resize(.Rows.Count - EndRow).Hidden = True
       End With
      Next n
    End Sub

    Microsoft MVP - Excel

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Redcliff, Alberta, Canada
    Thanked 5 Times in 5 Posts
    Here's a non-code method that will work if the rows are the same in every sheet.
    Group the sheets together by clicking on the first sheet tab and shift + clicking on the last sheet tab. The sheet tabs will change colour to confirm the sheets are grouped. Now hide your rows. Right-click on any sheet tab and select ungroup sheets.

    When sheets are grouped together, formatting actions are applied to all sheets simultaneously.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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