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.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
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.
- 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.
- 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 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.
- 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.
- 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.
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.
How to Highlight Every Other Row in Excel
Viewing Excel Worksheets in Multiple Ways