Unhide All Hidden Sheets

Excel supports hiding multiple sheets in one go but you can unhide only one sheet at a time. As part of our VBA learning, today we are going to code a macro which actually does something useful, unhide all sheets with one action. We are going to use our macro recorder to see what code Excel use to hide / unhide a sheet and expand it to suit our requirement. I hope you remember how to record a macro.

Record Hiding a Sheet

Create a new workbook and save it as macro enabled workbook.

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

• Enter "Macro Name" as HideSheet (any name you want for that matter, just follow rules)
• Click OK.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier. Now your macro started recording.

Right click on a sheet tab and click on Hide. This will hide the tab. Now right click again and click on Unhide > click on sheet name you have hidden in previous step and click OK. Your macro would have recorded both actions, let’s go and check it out. Before that, stop macro recording by clicking on that blue button.

Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here). In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window. My recorder shows the below code in Code Window.

Sub HideSheet()
'
' HideSheet Macro
'
 '
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Sheet2").Select
    Sheets("Sheet1").Visible = True
End Sub

We know that the VBA recorder code is not perfect. Let’s first analyse the code to see what it does.

• Sheets("Sheet1").Select selects the sheet.
• ActiveWindow.SelectedSheets.Visible = False changes the visibility of the selected sheets to false i.e. this is the code to hide the sheet.
• Sheets("Sheet2").Select selects the sheet.
• Sheets("Sheet1").Visible = True changes the visibility of the sheet to true i.e. this is the code to unhide the sheet.

Remember, there is no need to select an object in order to work with it. But Excel does while recording which we can totally avoid. So effectively, point 1 and 2 can be combined into one. Point 3 can be totally removed. Let’s see our modified code.

Sub HideSheet()
    Sheets("Sheet1").Visible = False 'hide the sheet
    Sheets("Sheet1").Visible = True 'unhide the sheet
End Sub

Now that we know which object’s what property we need to alter to hide / unhide a sheet, let’s proceed to find out how to do the same on multiple sheets.

Before we continue, you have to learn a bit about Sheets object. Sheets object is a collection of all sheets in the workbook. You can refer any particular sheet in the workbook by accessing it from the Sheets collection. There are multiple ways to refer a sheet from the Sheets object. One way is to call out using the sheet name as in above code snippet.

Sheet(“Sheet1”)

This calls the sheet named Sheet1 no matter where this sheet is placed in set of sheets. Another method is to refer to number of the sheet.

Sheet(1)

This refers to first sheet in the order, no matter what its name is. You can follow both the ways based on your convenience. I use index number, because most of the times you may not know what will be the sheet name.

Now that we know how to refer sheets from Sheets object, let’s see how we can change visibility property for all sheets.

Method 1: Using a Counter

We will use a variable as counter & For Next statement to repeat the macro for all sheets.

Sub UnhideAllSheets()
Dim A As Integer
For A = 1 To Sheets.Count
    Sheets(A).Visible = True
Next A
End Sub

Copy the above code and place it in code window. Run this macro, you will be able to see all the sheets in your workbook visible.

I am using a variable A as an index number to refer the sheet. Also same variable is used to repeat the macro for those many times as the number of sheets in the workbook. Number of sheets in workbook is retrieved by Sheets.Count method. I used For Next statement to loop through.

Method 2: Using For Each Statement

Another way to get this done is to loop using For Each statement. For this we use a variable to contain each sheet.

Sub UnhideAllSheets2()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    Sht.Visible = True
Next Sht
End Sub

In above code snippet, we are declaring a variable Sht to contain all the properties of a sheet. Then using For Each statement, we assigned first sheet to Sht. Changing Sht properties now will change first sheet properties. Then the Sht will be assigned with second sheet and its visible property will be changed. This process continues till all the sheets visibility property is changed.

Above utility will unhide all the hidden sheets (including very hidden). Of course, you can customize the macro to unhide only normal hidden sheets or only very hidden sheets or any other criteria you want. Just explore the possibilities. Happy Learning!!

You may also want to read about:

Very Hidden Sheets