delete all hidden sheets 1

[avayaman]

I have Excel Workbooks being used for PBX Switch implementaion. They contain dozens of different templates, not all of which are used on any particular project. You simply unhide the ones that are used. The final results are stored on a LAN and the unused hidden sheets add to the total file size. I have found a cumbersome way to delete them, but I was wondering if anyone knows a simple macro that would delete all hidden sheets simply.

 

[combo]

Sub UnhideSheets()
For Each sh In Workbooks(1).Sheets
If sh.Visible <> xlSheetVisible Then sh.Visible = xlSheetVisible
Next sh
End Sub

Instead of 1 can be the name of workbook

 

[combo]

Ooops, this was not the answer.
Try that:

Sub DelHiddenPages()
On Error Resume Next
Application.DisplayAlerts = False
For i = Workbooks(1).Sheets.Count To 1 Step -1
If Sheets(i).Visible <> xlSheetVisible Then
Sheets(i).Visible = xlSheetVisible
Sheets(i).Delete
End If
Next i
Application.DisplayAlerts = True
End Sub

 

[avayaman]

I cut & paste the code in, but the hidden sheets are still there

 

[combo]

Hi beddows,

Some changes in the code and explanations.
Previous code can operate only within the workbook where it is placed. The below should work without this limitation.

Assumed that the workbook you want to remove pages is &quot;Wbk1.xls&quot;.

Sub DelHiddenPages()
Application.DisplayAlerts = False
On Error Resume Next
With Workbooks(&quot;Wbk1&quot
For i = .Sheets.Count To 1 Step -1
If .Sheets(i).Visible <> xlSheetVisible Then
.Sheets(i).Visible = xlSheetVisible
.Sheets(i).Delete
End If
Next i
End With
Application.DisplayAlerts = True
End Sub

Some remarks:
1. I added some code to run macro quiet. Property DisplayAlerts set to False removes annoying questions concerning confirmation to delete page. Line On Error Resume Next allows macro to continue if worksheetsheet is protected and sheets cannot be deleted. If you comment those lines, you will have more information on macro running.
2. I refer to the workbook name by &quot;Wbk1&quot; (excel XP), however according to the help file should be &quot;Wbk1.xls&quot; (this does not work).
3. Before deleting page I make it visible. This is because you can't delete page if it is &quot;very hidden&quot; (error occures).
4. You can't delete pages if the structure of the workbook is protected.
5. The workbook &quot;Wbk1.xls&quot; should be open before you run code. However, with some additional code this can be automated (for instance using build in dialog xlDialogOpen).

Hope this will work. Sorry for not precise previous answer.

 

[avayaman]

Thanks Combo,

That works like a charm. The only problem was that these sheets are templates, so the names of them change each time, but experimenting around I found that changing the line with workbooks (&quot;wkb1&quot to with workbooks.parent made it generic to the actual workbook name. Thank you again.

 

[JTuc]

Beddows,
I'm not quite as advanced as you are, so I don't quite get this. Can you explain to me (laymans term please) where it is that you copy and paste these codes?

 

[avayaman]

Easiest way is to open the workbook, go to Tools/Macro/record new macro. Click Ok and then hit the stop button when the stop box appears. Copy the code above, go back into tools/macros and select the macro you recorded (Usually named macro1) & replace it with the copied code. You will have to assign to to a control button or something, unless you just want to invoke it manually by goint to tool/macro run.