Delete Hidden Sheets

[Lee]

Below is a free macro widely available on numerous sites to delete hidden
sheets in a workbook:

Sub Delete_Hidden_Sheets()

' Remove hidden sheets from your document
i = 1
While i <= Worksheets.Count
If Not Worksheets(i).Visible Then
Worksheets(i).Delete
Else
i = i + 1
End If
Wend
End Sub

Problem I'm having is that it works fine if I delete sheets, but if I decide
to cancel using the "Cancel" button from the warning message, it goes to the
"End If" then "Wend" then cycles again coming back to the warning message
again. Only way to break free is kill the macro by ctrl-break, then end.
Any ideas on how to cancel and have it stop the macro?

Thanks

 

[Jacob Skaria]

Try disabling the alerts...

Sub Delete_Hidden_Sheets()
Application.DisplayAlerts = False

'place the rest of the code here

Application.DisplayAlerts = True
End Sub

If this post helps click Yes

 

[Rick Rothstein]

You can use this macro instead and it won't get trapped in a loop...

Sub Delete_Hidden_Sheets()
Dim WS As Worksheet
For Each WS In Worksheets
If Not WS.Visible Then WS.Delete
Next
End Sub

However, if you have more than one sheet hidden, how will you know which
sheet the warning message is for?

 

[Rick Rothstein]

How about using this macro instead (it shows you the sheet name and asks if
you want to delete it)?

Sub Delete_Hidden_Sheets()
Dim WS As Worksheet, Answer As Long
Application.DisplayAlerts = False
For Each WS In Worksheets
If Not WS.Visible Then
Answer = MsgBox("Sheet Name: " & WS.Name & vbLf & vbLf & _
"Do you want to delete this sheet?", vbYesNo)
If Answer = vbYes Then WS.Delete
End If
Next
Application.DisplayAlerts = True
End Sub