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.
- All worksheets removed CANNOT be recovered. This action CANNOT be undo. Please save your file as a separate file before you perform this action.
- 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. 🙂
If hidden sheets are removed, most likely the spreadsheet model will be broken as stated. What we need is a method of making the hidden sheets visible for inspection purposes, without running VBA.
No need to enable and run macros in suspicious workbooks with hidden sheets. Hidden sheets can be made visible in closed workbooks even without removing Workbook protection! Use this free add-in:
Thanks for sharing, Petros 😀
In Excel 2016 the inspector does delete very hidden sheets as well.
That would be awesome! Thanks for telling me 😀