Hide Rows on Multiple Worksheet Pages

mangier · Sep 6th 2006

This code will hide row 2 across Sheet1, Sheet2 and Sheet3 while recording in the macro record mode but when played back after recording will only hide row 2 on Sheet1. How do I get this code to work?

Code

Sub MultiplePageHideRows()
    Rows("2:2").Select
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Sheets("Sheet1").Activate
    Selection.EntireRow.Hidden = True
    Range("A1").Select
    Sheets("Sheet1").Select
End Sub

**royUK** · Sep 6th 2006

Re: Hide rows on multiple pages

try this

Code

Option Explicit


Sub MultiplePageHideRows()
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Rows(2).EntireRow.Hidden = True
End Sub

StefanG · Sep 6th 2006

Re: Hide rows on multiple pages

Hi,
PMFJI
I found this to be working for me.

Code

Sub MultiplePageHideRows1()
Dim I
    For I = 1 To 3
        Sheets("Sheet" & I).Activate
        Rows("2:2").EntireRow.Hidden = True
        Range("A1").Select
    Next I
    Sheets("Sheet1").Select
End Sub

Stefan

mangier · Sep 7th 2006

Re: Hide rows on multiple pages

The 1st code suggestion does not work,

Code

Sub MultiplePageHideRows() 
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select 
    Rows(2).EntireRow.Hidden = True 
End Sub

The second approach works but is tedious. My sheets all have names so I would have to cycle through 15 named sheets to hide a given row on each sheet. The row being hidden is based on a cell value in a row on sheet1 of the 15 named sheets.

There must be an easier way to hide the same row across multiple sheets without having to cycle through each sheet to do it. Why can you copy and paste across multiple sheets with the VBA Array structure but you can't hide rows. Very baffling.

StefanG · Sep 7th 2006

Re: Hide rows on multiple pages

Hi,

Quote

cycle through 15 named sheets

I figured so much ;-). How about this?

Code

Sub MultiplePageHideRows2()
Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
        WS.Activate
            Rows("2:2").EntireRow.Hidden = True
            Range("A1").Select
    Next
    Sheets("Sheet1").Select
End Sub

Dave Hawley · Sep 7th 2006

Re: Hide rows on multiple pages

How about

Code

Sub MultiplePageHideRows()
    Dim wSheet As Worksheet
    
    For Each wSheet In ActiveWorkbook.Worksheets
        Select Case UCase(wSheet.Name)
            Case "SHEET1", "SHEET2", "SHEET" 'Add more buts use CAPS
                wSheet.Rows("2:2").EntireRow.Hidden = True
            Case Else
        End Select
    Next wSheet
End Sub

Display More

Hide Rows on Multiple Worksheet Pages

Participate now!

Share