Batch delete hidden worksheets

  • Thread starter Thread starter escribe
  • Start date Start date
E

escribe

I have over 50 hidden worksheets (in 1 workbook) that I want to delete.
Is there a shortcut to either delete all hidden sheets or at leas
unhide them in a batch?

I don't have VBA skills.

Thanks for any help
 
something like this

Sub deletehiddenwbs()
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Visible <> True Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub

Don Guillett
SalesAid Software
(e-mail address removed)
 
Try the sub below on a *back-up* copy of your file

Steps
------
1. Press Alt + F11 to go to vba
2. Click Insert > Module
3. Copy > Paste the sub below (everything between the dotted lines)
into the white empty space on the right-side

------------begin vba----------
Sub DeleteHiddenSheets()
'Run on a *back-up* copy
'Deletes all hidden sheets
'without any prompts

Dim s As Worksheet
Application.DisplayAlerts = False
For Each s In ActiveWorkbook.Worksheets
If s.Visible = False Then
s.Delete
End If
Next
Application.DisplayAlerts = True
End Sub
--------end vba---------

4. Press Alt + Q to return to excel

5. Press Alt + F8 (or click Tools > Macro > Macros)

In the dialog box:

Click on "DeleteHiddenSheets" > Run
(or just double-click on DeleteHiddenSheets)
 
Since you also asked for ".. a shortcut to .. unhide all hidden sheets in a
batch .."

Below's a sub which will unhide all hidden sheets at one go:

Install & run in the same manner as per steps given
for the earlier Sub DeleteHiddenSheets()

---------begin vba------
Sub ShowSheets()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
s.Visible = True
Next
End Sub
------end vba------
 
Thanks to Don and Max for your kind reply and routines to perform this
batch process. I happily used your code and was able to fix my
spreadsheet lickety split! The size of the file went from >4500 kbs to
870 kbs and it works as well as before.

Another question is since I added this macro, when I open the
worksheet, the warning about enable/disable macros comes up.

Can you suggest how to turn this off. Since I'm not using any imported
data in this spreadsheet, I don't see the need for this warning.
 
You're welcome, Escribe!
Thanks for feedback.

As for your follow-on Q:
Another question is since I added this macro, when I open the
worksheet, the warning about enable/disable macros comes up.

Can you suggest how to turn this off. Since I'm not using any imported
data in this spreadsheet, I don't see the need for this warning.
Click to expand...

I'm using xl 97, and it seems that it's not possible in xl 97 to turn it
off,
re: a previous reply by MVP Gord Dibben pasted below.

Perhaps you would like to state what's your xl version
for others to chip-in to clarify ?
--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

From: Gord Dibben ([email protected])
Subject: Re: Disable macro warning
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.excel.setup
Date: 2001-07-09 11:48:22 PST

Cannot be done in XL97. All or nothing as far as Macro warnings are
concerned. If there was a way to do it, virus writers could have a field
day.

Upgrade to XL2000 and get the ability to have low to high security levels
and
signed certificates allowing certain books to be opened without the warning.

HTH Gord XL97 SR2

In Excel 97 is there a way through a command-line switch
or something similar to disable the Macro warning when
opening a specified worksheet?

In general I'd like to keep the macro warnings to adhere
to good security practices, but I have a daily report with
an embedded ActiveX object that I open regularly, and I
would like to open it from a shortcut without the macro
warning [obviously I trust it].

Any advice is appreciated.
Click to expand...
-------------------------------
 
Glad to help. The macro message comes up due to your macro security settings
in tools>options.
 
Back
Top