Remove hidden sheets with “Inspect Document” or VBA

This post is an extension of the previous one – we talked about hiding worksheets is not a good way to prevent others from seeing the data on hidden sheets.  Since you have read that post, you decided to remove all hidden sheets.  Then you realized you had to unhide them one by one before you could delete them… Is that really the case?

Well… it depends.
Indeed there is a way to remove all hidden sheets (just hidden, not very hidden) quickly.

Go to File –> Info –> Check for Issues –> Inspect Document (don’t be surprise if you haven’t used that before, I am sure you are not alone)

Excel Tips - Remove Hidden Sheets

The “Document Inspector” is opened…

You may simply click “Inspect“, or scroll all the way down to see what are included in this Window:

Excel Tips - Remove Hidden Sheets1

After the inspection, which takes a flash of second, you will see the number of  “Hidden Worksheets” found.

And you are provided an option to “Remove All”…

Wait… Continue reading before pressing that icon.

Excel Tips - Remove Hidden Sheets2

IMPORTANT NOTES:

  1. All worksheets removed CANNOT be recovered.  This action CANNOT be undo. Please save your file as a separate file before you perform this action.  
  2. If there are formula referencing to the hidden sheet(s), they will return #REF! afterwards.  You may want to convert all formula to value first. 

When you are ready to remove all hidden sheets, simply click “Remove All”

Then you will see:

Excel Tips - Remove Hidden Sheets3

However, it doesn’t work if you have “very hidden sheets”.

Excel Tips - Remove Hidden Sheets4

What to do if you have “very hidden sheets”?

VBA to rescue. The following lines of VBA code would save your time.

IMPORTANT:  DO NOT run the following codes on your original file as the action is irreversible!  Always test it on a copy!

Sub DeleteHiddenSheets()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets
 
 If Not ws.Visible Then
 ws.Visible = xlSheetVisible
 ws.Delete
 End If
 
 Next ws

Application.DisplayAlerts = False

End Sub

As you may know, I am not good in VBA at all.  If you have other suggestions, you are more than welcome to leave your comment.   🙂

 
Online Excel VBA Training

Advertisements
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

4 Responses to Remove hidden sheets with “Inspect Document” or VBA

  1. harsha547 says:

    Thank You

    Like

  2. FrankT says:

    In Excel 2016 the inspector does delete very hidden sheets as well.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s