How to Delete/Remove Hidden Worksheets in Excel?

If there are some hidden worksheets exist in excel and you want delete them as they are not useful, you can unhide all worksheets and remove them one by one. This way is quite annoying and complex. If we can remove all hidden worksheets by only one step or a simple way, it will be much better.

This post will guide you on how to delete or remove hidden worksheets in Excel using two different methods: the VBA code and the Inspect Document feature. Please read the article below, let’s get started.

1. Video: Delete or Remove Hidden Worksheets

The below tutorial video will show demonstrate how to delete or remove hidden worksheets using VBA code or the Inspect Document feature.

2. How to View Hidden Sheets in Excel

Excel allows users to hide and unhide sheets to protect sensitive data or to reduce clutter in a workbook. To view hidden sheets, you can use the following methods:

Using the Excel Ribbon:

Open the workbook and click on the “Home” tab in the ribbon. Click on the “Format” dropdown arrow in the “Cells” group. Then select “Hide & Unhide” and then choose “Unhide Sheet“.

Select the sheet that you want to unhide from the list and click “OK.”

Using the Right-Click Context Menu:

Right-click on any sheet tab in the workbook.  And select “Unhide” from the context menu. Select the sheet that you want to unhide from the list and click “OK.”

3. Delete/Remove Hidden Worksheets by VBA

VBA code can help you to remove all hidden worksheets in one workbook properly. So, this method is very helpful. You can copy below code directly.

Step1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

Step2: In Microsoft Visual Basic for Applications window, click Insert->Module.

Step3: Enter below code in Module1, Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Sub RemoveAllHiddenSheets()
Dim i As Integer
  Application.DisplayAlerts = False
  For Each sht In ActiveWorkbook.Sheets
      If sht.Visible = xlSheetHidden Then
        sht.Delete
        i = i + 1
      End If
  Next sht
  Application.DisplayAlerts = True
End Sub

Step 4: Before run macro, let’s see if there are unhide worksheets exist in this workbook.

Unhide is enabled in menu so there are some hidden worksheets in this workbook.

Step 5: On current sheet, click Developer->Macro, in Macro window, select Macro ‘RemoveAllHiddenSheets’, then click Run.

Step 6: Check if the hidden worksheets are removed or not.

Unhide is disabled so there is no hidden worksheet now.

Note: If you want to get some message when running macro to delete hidden worksheets, you can add below code into module. Then you can get some message prompts when executing the macro.

Sub RemoveAllHiddenSheets()
Dim i As Integer
  Application.DisplayAlerts = False
  For Each sht In ActiveWorkbook.Sheets
      If sht.Visible = xlSheetHidden Then
        sht.Delete
        i = i + 1
      End If
  Next sht
  Application.DisplayAlerts = True
    If i = 1 Then
    MsgBox "[1] sheet is removed from this workbook."
  Else
    MsgBox "[" & i & "] sheets are removed from this workbook."
  End If
End Sub

Run the above VBA macro this time, verify that below message pops up to indicate that some sheets are removed.

4. Delete/Remove Hidden Worksheets by Inspect Document Function

This method is helpful for the users who are not skilled in code editing. And they can follow below steps to remove all hidden worksheets. All operations are conveniently to operate.

Step1: In Excel ribbon, click File->Info->Check for Issues->Inspect Document.

Step2: Click Yes on pops up message.

Step3: On pops up Document Inspector, click Inspect.

Step4: On current Document Inspector window, drag the scrollbar to the end, verify that Hidden Worksheets is listed, click Remove All.

Step5: Verify that all hidden worksheets were removed. Then click Close.

Step6: Right click on sheet name tab to double check, verify that Unhide is disabled in menu, that means there is no hidden worksheet now.

5. Conclusion

Deleting or removing hidden worksheets in Excel can be accomplished through different methods, including using VBA code or the Inspect Document feature. After reading this article, I think that you will be able to remove hidden worksheets with ease and make the most of your Excel files.