Md. Sourov Hossain Mithun
If you try to delete your hidden sheets after unhiding them, then it will consume time. Excel has some amazing features by which you can directly delete your hidden sheets smartly and quickly without unhiding them. This article will provide you with these 2 smart methods to delete hidden sheets in Excel.
Download Practice Workbook
You can download the free Excel template from here, and practice on your own.
2 Methods to Delete Hidden Sheets in Excel
To explore the methods, we’ll use the following dataset that represents some salespersons’ sales in different regions.
1. Remove Hidden Sheets Applying Inspect Document Function in Excel
Have a look that there are some hidden sheets in my workbook that’s why it is showing the Unhide option. So in our very first method, we’ll use the Inspect Document function to delete all the hidden sheets.
Steps:
- Click the File tab.
- Then click as follows: Info > Check for Issues > Inspect Document.
And soon after a Document Inspector dialog box will open up.
- At this moment, press Inspect.
- Then scroll down and select Remove All from the Hidden Worksheets section.
- Finally, just press Close to quit the dialog box.
Now take a look that the Unhide option is blurred which means all the hidden sheets are deleted.
Read More: How to Delete a Sheet in Excel (5 Ways)
Similar Readings:
- [Fixed!] Delete Sheet Not Working in Excel (2 Solutions)
- Excel VBA: Delete Sheet If It Contains Name (4 Ways)
- How to Delete a Worksheet with No Prompt Using Excel VBA (5 Methods)
- Delete Extra Pages in Excel (4 Methods)
2. Delete Hidden Sheets Using VBA Macros in Excel
If you like to work with code in Excel then you can easily delete all the hidden sheets using VBA Macros. I’ll show you 3 different macros to do the task.
Macro 1: Delete with a Confirmation Pop-up
Steps:
- Right-click on any sheet title.
- Then select View Code from the Context menu.
Soon after, a VBA window will appear. Also, you can press Alt+F11 to open the VBA window directly.
- Then write the following codes 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- Finally, just press the Run icon to run the codes.
A pop-up notification box will open up to ensure your deletion.
- Press Delete.
Now Excel has deleted all hidden sheets and made the Unhide option blurred.
Macro 2: Delete with a Confirmation Message
Let’s use another macro to delete hidden sheets. It will give you a confirmation message before deleting them.
Steps:
- Open the VBA window by clicking the View Code option from the Context menu after right-clicking on any sheet title.
- Write the following codes 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- Then press the Run icon for running the codes.
A confirmation dialog box will open up.
- Press Yes to confirm.
And Yes! All the hidden sheets are deleted, no Unhide option.
Macro 3: Delete without Any Prior Confirmation
Here, we’ll use another macro to delete hidden sheets in Excel. But this macro will not give you any confirmation message before deleting hidden sheets.
Steps:
- Select View Code from the Context menu after right-clicking on any sheet title.
- After opening the VBA window, type the following codes in it-
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- Later, click the Run icon to run the macro.
No Unhide option, means no hidden sheets exist.
Read More: How to Delete Excel Sheet Using VBA (10 Suitable Ways)
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 then close the workbook without saving it and open the file again. Then you will get back the sheets.
Conclusion
I hope the procedures described above will be good enough to delete hidden sheets in Excel. Feel free to ask any question in the comment section and please give me feedback.
