Excel Macro: Unhiding All Worksheets

Unhiding a lot of sheets is annoying task. So, I’ve made this Excel VBA macro.
This macro unhides all the worksheets in an active workbook.

Macro Example

Sub UnhideAllWorksheets()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Visible = xlSheetVisible 
  Next sh

End Sub

Description

Variable Declaration

The 3rd line declares an object variable called sh which will refer to a Worksheet object.

Sub...

  Dim sh As Worksheet

The Worksheet object represents a worksheet in an Excel workbook.

obj-Excel-Worksheet

Starts Looping

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets

The 5th line starts to loop through all the elements (=Worksheet) of a collection (=Worksheets).

[Application.]Worksheets

The Application.Worksheets property returns the Sheets collection object that represents all the worksheets (without chart-sheets) in the workbook.

obj-Excel-Application-Worksheets

The Worksheets property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Worksheets

Unhide Worksheets

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Visible = xlSheetVisible 

The 6th line unhide the worksheet by the Worksheet.Visible property.

Worksheet.Visible

The Worksheet.Visible property returns or sets an XlSheetVisibility value that determines whether it is visible.

obj-Excel-Worksheet-Visible

XlSheetVisibility Enumeration

The XlSheetVisibility enumeration specifies whether the object is visible.
That has these constants:

Name Value Description
xlSheetVisible -1 Displays the sheet.
xlSheetHidden 0 Hides the sheet which users can unhide via menu.
xlSheetVeryHidden 2 Hides the sheet but users cannot make the sheet visible.
The only way for us to make it visible again is by setting the Worksheet.Visible property to xlSheetVisible or True.

Loops Back

The 7th line loops back to get the next worksheet.

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Visible = xlSheetVisible 
  Next sh

After every worksheet is unhidden, this macro ends.

Sub UnhideAllWorksheets()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Visible = xlSheetVisible 
  Next sh

End Sub

Properties

http://www.relief.jp/itnote/archives/001295.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007