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.

How to DeleteRemove Hidden Worksheets in Excel 10.png

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“.

show unhide worksheets 1.png

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

show unhide worksheets 2.png

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.”

show unhide worksheets 3.png

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.

How to DeleteRemove Hidden Worksheets in Excel 11.png

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

How to DeleteRemove Hidden Worksheets in Excel 11.png

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

How to DeleteRemove Hidden Worksheets in Excel 11.png

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

How to DeleteRemove Hidden Worksheets in Excel 12.png
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.

Delete Hidden Worksheets2

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.

Delete Hidden Worksheets3

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

Delete Hidden Worksheets4

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.

How to DeleteRemove Hidden Worksheets in Excel 13.png
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.

Delete Hidden Worksheets6

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.

Delete Hidden Worksheets71

Step2: Click Yes on pops up message.

Delete Hidden Worksheets8

Step3: On pops up Document Inspector, click Inspect.

Delete Hidden Worksheets9

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

Delete Hidden Worksheets10

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

Delete Hidden Worksheets11

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.

Delete Hidden Worksheets12

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.