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.


How to Hide the Same Rows Across Multiple Excel Worksheets: 2 Macros

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

Hide the Same Rows Across Multiple Excel Worksheets


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.

Hiding All the Same Rows Across Multiple Excel Worksheets

  • 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.

Codes to Hide All the Same Rows Across Multiple Excel Worksheets

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.

Open Macro to Hide the Same Rows Across Multiple Excel Worksheets

  • 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.

Read More: VBA to Hide Rows in Excel


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.

Hiding Rows from Multiple Worksheets Based on a Cell Value

Steps:

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.

Insert Codes to Hide Rows from Multiple Worksheets Based on a Cell Value

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.

Select the Macro Name to Hide Rows from Multiple Worksheets Based on a Cell Value

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.

Read More: How to Hide Rows Based on Cell Value in Excel


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Hide Rows | Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo