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

Get FREE Advanced Excel Exercises with Solutions!

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.


Delete Hidden Sheets in Excel: 2 Methods

To explore the methods, we’ll use the following dataset representing 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.

Remove Hidden Sheets Applying Inspect Document Function in Excel

Steps:

  • Click the File tab.

Remove Hidden Sheets Applying Inspect Document Function in Excel

  • Then, click as follows: Info > Check for Issues > Inspect Document.

Soon after Document Inspector dialog box will open up.

section

  • At this moment, press Inspect.

  • Then scroll down and select Remove All from the Hidden Worksheets section.

Remove Hidden Sheets Applying Inspect Document Function in Excel

  • Finally, just press Close to quit the dialog box.

Now take a look at the Unhide option is blurred which means all the hidden sheets are deleted.

Read More: [Fixed!] Delete Sheet Not Working in Excel


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.

Delete Hidden Sheets Using VBA Macros in Excel

  • 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, press the Run icon to run the codes.

Delete Hidden Sheets Using VBA Macros in Excel

A pop-up notification box will open up to ensure your deletion.

  • Press Delete.

Delete Hidden Sheets Using VBA Macros in Excel

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.

Delete Hidden Sheets Using VBA Macros in Excel

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

Delete Hidden Sheets Using VBA Macros in Excel

A confirmation dialog box will open up.

  • Press Yes to confirm.

Delete Hidden Sheets Using VBA Macros in Excel

And Yes! All the hidden sheets are deleted, with 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 confirmation before deleting hidden sheets.

Steps:

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

Delete Hidden Sheets Using VBA Macros in Excel

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

Delete Hidden Sheets Using VBA Macros in Excel

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 Practice Workbook

You can download the free Excel template from here and practice on your own.


Conclusion

I hope the procedures described above will be good enough to delete hidden sheets in Excel. Feel free to ask any questions in the comment section, and please give me feedback.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo