How to Delete Hidden Sheets in Excel (2 Effective Methods)

Method 1 – Remove Hidden Sheets by Applying the Inspect Document Function in Excel

We’ve hidden some sheets in the workbook.

Steps:

Click the File tab.

Go to Info, then to Check for Issues, and select Inspect Document.
The Document Inspector dialog box will open up.

Press Inspect.

Scroll down and select Remove All from the Hidden Worksheets section.

Press Close to quit the dialog box.

The Unhide option is blurred which means all the hidden sheets are deleted.

Method 2 – Delete Hidden Sheets Using VBA Macros in Excel

Macro 1 – Delete with a Confirmation Pop-up

Steps:

Right-click on the sheet title.
Select View Code from the Context menu.
A VBA window will appear. You can press Alt+F11 to open the VBA window directly.

Insert the following code in the VBA window-

Sub Delete_Hidden_Sheets()
j = 1
While j <= Worksheets.Count
If Not Worksheets(j).Visible Then
Worksheets(j).Delete
Else
j = j + 1
End If
Wend
End Sub

Press the Run icon to run the code.

A pop-up confirmation box will open.
Press Delete.

Excel has deleted all hidden sheets and made the Unhide option blurred.

Macro 2 – Delete with a Confirmation Message

Steps:

Open the VBA window by clicking the View Code option from the Context menu after right-clicking on any sheet title.

Insert the following code in it:

Sub Delete_Hidden_Sheets()
Dim x As Worksheet
ConfirmMacro = MsgBox("Are you sure to delete all the hidden sheets?", vbYesNo, " CONFIRMATION! ")
    If ConfirmMacro = vbNo Then
    Exit Sub
    Else
End If
With Application
  .DisplayAlerts = False
  For Each x In Worksheets
    If x.Visible = xlVeryHidden Then x.Visible = Hidden
    If Not x.Visible Then x.Delete
  Next x
  .DisplayAlerts = True
End With
End Sub

Press the Run icon.

A confirmation dialog box will pop up.
Press Yes to confirm.

All the hidden sheets are deleted, with no Unhide option.

Macro 3 – Delete without Any Confirmation

Steps:

Select View Code from the Context menu after right-clicking on any sheet title.

After opening the VBA window, insert the following code:

Sub Remove_Hidden_Sheets()
Dim j As Integer
Dim X as Worksheet
  Application.DisplayAlerts = False
  For Each X In ActiveWorkbook.Sheets
      If X.Visible = xlSheetHidden Then
        X.Delete
        j = j + 1
      End If
  Next X
  Application.DisplayAlerts = True
End Sub

Click the Run icon to run the macro.

No Unhide option means no hidden sheets exist.

Things to Remember

After deleting hidden sheets, there is no way to undo or recover them. So, make sure you have created a backup.
If you haven’t saved the file after deleting it, close the workbook without saving it and open it again. Then you will get back the sheets.

Download the Practice Workbook

Delete Hidden Sheets.xlsm