Excel Macro: Deleting Hidden Worksheets

This Excel VBA macro allows us to delete hidden worksheets from an active workbook.

Macro Example

Sub DeleteHiddenWorksheets()

  Dim sh As Worksheet

  Application.DisplayAlerts = False

  For Each sh In Worksheets
    If sh.Visible = xlSheetHidden Then 
      sh.Delete
    End If
  Next sh

  Application.DisplayAlerts = True

End Sub

Description

Variable Declaration

The 3rd line declares an object variable named sh represents a Worksheet object.

Sub...

  Dim sh As Worksheet

The Worksheet object represents a worksheet in an Excel workbook.

obj-Excel-Worksheet

Suppresses Messages

The 5th line suppresses messages by the Application.DisplayAlerts property.

Sub...

  Dim sh As Worksheet

  Application.DisplayAlerts = False

If we set the Application.DisplayAlerts property to False, Excel does not display alerts and messages while a macro is running.

obj-Excel-Application-DisplayAlerts

Starts Looping

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

Sub...

  Dim sh As Worksheet

  Application.DisplayAlerts = False

  For Each sh In 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

Checks Hidden

The 8th line checks if the worksheet is hidden by the Worksheet.Visible property.

Sub...

  Dim sh As Worksheet

  Application.DisplayAlerts = False

  For Each sh In Worksheets
    If sh.Visible = xlSheetHidden Then

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.

Delete the Hidden Worksheets

If the worksheet is hidden, the 9th line deletes it by the Worksheet.Delete method.

Sub...

  Dim sh As Worksheet

  Application.DisplayAlerts = False

  For Each sh In Worksheets
    If sh.Visible = xlSheetHidden Then
      sh.Delete
    End If

obj-Excel-Worksheet-Delete

Loops Back

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

Sub...

  Dim sh As Worksheet

  Application.DisplayAlerts = False

  For Each sh In Worksheets
    If sh.Visible = xlSheetHidden Then 
      sh.Delete
    End If
  Next sh

After every worksheet is evaluated, this macro ends.

Sub DeleteHiddenWorksheets()

  Dim sh As Worksheet

  Application.DisplayAlerts = False

  For Each sh In Worksheets
    If sh.Visible = xlSheetHidden Then 
      sh.Delete
    End If
  Next sh

  Application.DisplayAlerts = True

End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007