Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hidden Sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hidden Sheets
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 --------------- Jacob Skaria "Lee" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hidden Sheets
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hidden Sheets
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"With Sheets" Issue - macro on one sheet to affect hidden rows on other sheets | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming |