How To Hide Unhide Multiple Excel Worksheets Manually And Programmatically

This article will tell you how to hide and unhide Excel worksheets manually and using VBA. After you write the VBA macro to hide/unhide the Excel worksheets, you can also add an icon on the Excel quick access toolbar to hide/unhide worksheets easily and quickly.

1. How To Hide / Unhide Excel Worksheets Manually.

1.1 How To Hide One Or Multiple Excel Worksheets Manually.

1. If you only want to hide one selected Excel worksheet, you can just right-click the worksheet tab at the Excel bottom.
2. Then click the Hide menu item in the popup menu list.
3. If you want to hide multiple Excel worksheets, you can press the Shift / Ctrl key, then click each Excel worksheet tab on the Excel bottom to select them, then right-click one worksheet tab and click the Hide menu item in the popup menu list.

1.2 How To Unhide One Or Multiple Excel Worksheets Manually.

1. Right-click any worksheet tab on the Excel bottom area.
2. Then click the Unhide… menu item in the popup menu list.
3. If there is no worksheet tab on the Excel bottom area, then click the plus icon to add one.
4. Then it will pop up the Unhide dialog.
5. Click to select the worksheet in the Unhide sheet list, please note you can just select one worksheet at one time.
6. Then click the OK button, then it will show the hidden worksheet.

2. How To Hide / Unhide Excel Worksheets Programmatically.

2.1 Hide / Unhide Excel Worksheets Using VBA.

1. Right-click any Excel worksheet, then click the View Code menu item in the pop-up menu list to open the VBA window.
2. You can also open the VBA window by clicking the Developer tab —> Visual Basic icon in the ribbon.
3. Then input the below VBA source code in the right side source code editor window.

   ' This function will hide all excel worksheets in current workbook.
   Sub hideAllWorksheets()
   
       ' Define a counter variable.
       Dim i As Integer
       
       ' Initialize the counter to 1.
       i = 1
       
       ' Loop in all current workbook worksheets.
       For Each sh In Worksheets
       
           ' Hide the worksheet by set it's Visible property to False.
           sh.Visible = False
                
           ' If there are only one worksheet is visible, then exist the for loop.
           If i = Worksheets.Count - 1 Then
                
              Exit For
                   
           End If
           
           
           ' Add 1 to the worksheet counter.
           i = i + 1
           
       ' Get the next worksheet.
       Next sh
                
   End Sub
   
   ' This function will unhide all the worksheets in the current workbook.
   Sub unhideAllWorksheets()
   
       ' Loop in all the current workbook's worksheets.
       For Each sh In Worksheets
         
            ' Set the worksheet object's Visible property to True to unhide the worksheet.
            sh.Visible = True
                
       ' Get the next worksheet.
       Next sh
   
   End Sub

2.2 How To Add Icons In Excel Quick Access Toolbar To Hide/Unhide Worksheets Flexible.

1. You can read the article How To Run A Macro Using A Button On The Excel Quick Access Toolbar to learn more.