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
--
Rick (MVP - Excel)
"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
> 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 (MVP - Excel)
>
>
> "Lee" <(E-Mail Removed)> wrote in message
> news:588A68A0-7545-4741-9062-(E-Mail Removed)...
>> 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
>> --
>> Lee
>
|
Similar Threads
