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.
- If you only want to hide one selected excel worksheet, you can just right-click the worksheet tab at the excel bottom.
- Then click the Hide menu item in the popup menu list.
- 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.
- Right-click any worksheet tab on the excel bottom area.
- Then click the Unhide… menu item in the popup menu list.
- If there is no worksheet tab on the excel bottom area, then click the plus icon to add one.
- Then it will pop up the Unhide dialog.
- Click to select the worksheet in the Unhide sheet list, please note you can just select one worksheet for one time.
- 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.
- Right-click any excel worksheet, then click the View Code menu item in the pop-up menu list to open the VBA window.
- You can also open the VBA window by clicking the Developer tab —> Visual Basic icon in the ribbon.
- 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.
- You can read the article How To Run A Macro Using A Button On The Excel Quick Access Toolbar to learn more.