Delete All Hidden Sheets

What it does

Workbooks accumulate hidden sheets the way a desk accumulates paperwork: nobody intended it but here we are. Old staging tabs, abandoned scratch sheets, prototypes that "we'll come back to". Before sharing the workbook externally, you want them gone. The macro lists every hidden and very-hidden sheet, asks you to confirm, and deletes them all. The active workbook is modified in place.

Real-world example

Picture a controller at a private equity portfolio company about to send a financial model to the deal team for due diligence. The model has 18 visible tabs that are the actual deliverable, and another 14 hidden tabs that are working scratch from the past two years: an old "version 2" of the consolidation, three "test before posting" tabs, two empty placeholders, the rest archived calculations.

She wants the audit copy slim. The hidden tabs don't ship.

Her current method: right-click each tab, Unhide, pick the next hidden tab, right-click that, Delete, confirm. Repeat 14 times. About 4 minutes of right-click-and-confirm, with 14 chances to fat-finger an unintended sheet.

With this macro she runs the tool, sees "Delete 14 hidden sheet(s) from this workbook?", clicks Yes, and the 14 sheets are gone in 1 second. The popup confirms count.

On a 32-tab workbook with 14 hidden, expect about 4 minutes saved per cleanup. The bigger win is no fat-finger risk because the macro processes only hidden tabs and never touches visible ones.

How to use

First time? See How to run our tools for the 30-second setup.

Save the workbook first. Deletion is irreversible without a backup.

1. Press Alt + F8 (Windows) or Option + F8 (Mac), pick DeleteAllHiddenSheets, click Run.
2. The macro counts hidden sheets and asks for confirmation.
3. Click Yes to delete or No to cancel.
4. Popup confirms how many sheets were deleted.

FAQ

Can I undo this?

No. Sheet deletion is not undoable in Excel. Always save the workbook before running so you can recover by closing without saving and reopening. We may add a "save backup automatically" option in a future version.

What if I have one hidden sheet I want to keep?

Unhide it first (right-click > Unhide), run the macro on the rest, then re-hide the one you wanted to keep. Or use Unhide All Sheets to see what's there before deciding.

Does it delete very-hidden sheets too?

Yes. Both xlSheetHidden and xlSheetVeryHidden are treated as hidden by the macro. Anything not at xlSheetVisible gets deleted.

What if the workbook is protected at the workbook level?

Workbook-level protection prevents sheet deletion. The macro will error. Unprotect via Review > Protect Workbook first.

What about macros and code modules in the deleted sheets?

Sheets and their associated VBA modules are deleted together. Code in the workbook's general modules (in the VBA editor's Modules folder) is not affected; only sheet-specific code modules are removed.

What to do next

This is a destructive macro. Save the workbook, run it once, verify the output looks right, and save again with a new name (e.g., _audit_copy). For a non-destructive alternative, use Unhide All Sheets to inspect, then manually delete only the ones you don't need.