How to Hide the Same Rows Across Multiple Excel Worksheets

The sample dataset has three sheets and contains sales data of different regions.

Macro 1 – Hiding All the Same Rows Across Multiple Excel Worksheets

The first macro will hide all the same specific rows from all the sheets from our workbook. We’ll hide rows from 10 to 12 in every sheet.

Steps:

Press ALT + F11 to open the VBA window.
Click Insert > Module to insert a new module.

Enter the following codes in the module.

Sub HideRows_from_AllSheets()
    Dim WoSheet As Worksheet
    Dim miRng As Range, mitRng As Range
    For Each WoSheet In ThisWorkbook.Worksheets
        With WoSheet
            Set miRng = .Range("A10:A12")
            For Each mitRng In miRng.Cells
                mitRng.EntireRow.Hidden = Len(mitRng.Text) = 0
            Next mitRng
        End With
    Next WoSheet
End Sub

Go back to your sheet.

Code Breakdown:

We created a Sub procedure- HideRows_from_AllSheets.
We declared some variables, WoSheet as Worksheet and miRng, mitRng as Range.
We have used the For Each statement to select all the worksheets and set the range of rows.
If you want to hide rows from specifically selected sheets, use this line instead of the 4th line of the above code- For Each ws In ThisWorkbook.Worksheets(Array(“Sheet1”, “Sheet3”, “Sheet5”)).

Click on Macros from the Developer ribbon.

After Macro dialog box opens up, choose the specified macro name and press Run.

The mentioned rows are hidden on every page. Here’s the output of sheet1.

The output of sheet2.

Read More: VBA to Hide Rows in Excel

Macro 2 – Hiding Rows from Multiple Worksheets Based on a Cell Value

We’ll hide some rows from multiple sheets based on a cell value, which means that if we get a specific value in a particular sheet, the macro will hide the selected rows from all the sheets of the workbook.

We’ll check Cell B4, if the cell contains ‘Salesperson’, the macro will hide rows 10 to 12 from every sheet of the workbook.

Steps:

Follow the first two steps from the first method to open the VBA window and insert a new module.
Enter the following codes in the module-

Sub HideRows_BasedOn_CellValue()
Dim WoSheet As Worksheet
Application.ScreenUpdating = False
For Each WoSheet In Worksheets
    beginRow = 10
    endRow = 12
    For Rowcnt = beginRow To endRow
        If Sheets("Based on a Cell Value").Range("B4").Value = "Salesperson" Then
            WoSheet.Rows(Rowcnt).EntireRow.Hidden = True
        Else
            WoSheet.Rows(Rowcnt).EntireRow.Hidden = False
        End If
    Next Rowcnt
Next WoSheet
Application.ScreenUpdating = True
End Sub

Go back to your worksheet.

Code Breakdown:

In the above code, we created a Sub procedure first- HideRows_BasedOn_CellValue.
Then declared a variable, WoSheet as Worksheet.
And applied the For Each statement to select the row range.
We have used the For loop and If statement to look up the base value in the specified worksheet.

Follow the 5th step from the first method to open up the Macros dialog box.
Select the macro name from the list and press Run.

As Cell B4 contains the value- ‘Salesperson’, so the rows from 10 to 12 have been hidden from the all worksheets. This is the output of the main sheet.