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)

The “Document Inspector” is opened…

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

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.

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:

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

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