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

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.

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.

And soon after a 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 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:


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, just 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, 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.

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.

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.


Related Articles

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo