Delete Multiple Sheets

[Stephen]

Hi All,

I have the following VB code for deleting a couple of sheets.

Sub DeleteSheets()
Application.DisplayAlerts = False
Sheets(Array(("Sheet1","Sheet2","Sheet3",)).Delete
Application.DisplayAlerts = True
End Sub

My problem is that when for example Sheets2 does not exist then it run
into an error. How can I get the code to carry on with deleting th
other Sheets?

Stephe

 

[Frank Kabel]

Hi
one way:
Sub DeleteSheets()
Application.DisplayAlerts = False
on error resume next
Sheets(Array(("Sheet1","Sheet2","Sheet3",)).Delete
on error goto 0
Application.DisplayAlerts = True
End Sub

 

[Stephen]

Thanks for the code Frank. It works well.

Stephen

 

[Tom Ogilvy]

I think you need to look again Stephen.

the errror will occur with

Sheets(Array("Sheet1","Sheet2","Sheet3")).Delete

with subscript out of range, so no sheets will be deleted. If that is what
you want, then the code Frank provided will skip the error. If you wanted
to delete any of the sheets in the list that do exist you would need to use
something like this:

Sub DeleteSheets()
Application.DisplayAlerts = False
On Error Resume Next
varr = Array("Sheet1", "Sheet2", "Sheet3")
For i = LBound(varr) To UBound(varr)
Sheets(varr(i)).Delete
Next
On Error GoTo 0
Application.DisplayAlerts = True
End Sub