How to Hide the Same Rows Across Multiple Excel Worksheets

Get FREE Advanced Excel Exercises with Solutions!

We can hide individually from every sheet manually. But when we have a lot of sheets in our dataset and then if we want to hide rows one by one from each sheet then it’s quite bothering and time-consuming. To overcome this situation, there’s no direct built-in command in Excel but we can apply VBA in this regard according to our required manner. So, today we’ll show 2 useful macros in this article to hide the same rows across multiple Excel worksheets.

Table of Contents hide

Download Practice Workbook

2 Macros to Hide the Same Rows Across Multiple Excel Worksheets

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

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

Conclusion

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Hide Same Rows.xlsm

2 Macros to Hide the Same Rows Across Multiple Excel Worksheets

To apply the macros, we’ll use the following dataset that has three sheets and contains some salesperson sales in different regions.

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

Our 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.
Later, click Insert > Module to insert a new module.

Then type 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

Then go back to your sheet.

Code Breakdown:

First, we created a Sub procedure- HideRows_from_AllSheets.
Then declared some variables, WoSheet as Worksheet and miRng, mitRng as Range.
Next, we 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 then use this line instead of the 4th line of the above code- For Each ws In ThisWorkbook.Worksheets(Array(“Sheet1”, “Sheet3”, “Sheet5”)).

After that, click on Macros from the Developer ribbon.

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

Now see, the mentioned rows are hidden on every page. Here’s the output of sheet1.

It’s the output of sheet2.

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

Now we’ll hide some rows from multiple sheets based on a cell value, which means if we get a specific value in a particular sheet then the macro will hide the selected rows from all the sheets of the workbook. Here we’ll check Cell B4, if the cell contains ‘Salesperson’ then 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.
Then write 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

After that, navigate 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.
Later, applied the For Each statement to select the row range.
After that, we used the For loop and If statement to look up the base value in the specified worksheet.

Next, 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.

Now take a look, as Cell B4 contains the value- ‘Salesperson’, so the rows from 10 to 12 have been hidden from the all worksheets. It’s the output of the main sheet.

The output of sheet1, you will get the same output in every other sheet.

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to hide the same rows across multiple Excel worksheets. Feel free to ask any question in the comment section and please give me feedback.