Delete every sheet higher than sheet(16) [VBA]

[Martin_H]

Hi,

I would like to adjust my macro (see the code at the bottom) so it will delete every worksheet higher than sheet(16). Sheet(16) can not be deleted.

Currently I am using this line, which I do not like.

With ThisWorkbook
.Sheets(17).DELETE: .Sheets(18).DELETE: .Sheets(19).DELETE: .Sheets(20).DELETE: .Sheets(21).DELETE: .Sheets(22).DELETE
End With

Also I am not sure if I should use "On Error Resume Next" or not.

Thank you guys.

Sub deleteSheetIfExists()
On Error Resume Next
With Application
.DisplayAlerts = False
With ThisWorkbook
.Sheets(17).DELETE: .Sheets(18).DELETE: .Sheets(19).DELETE: .Sheets(20).DELETE: .Sheets(21).DELETE: .Sheets(22).DELETE
End With
.DisplayAlerts = True
End With
On Error GoTo 0
End Sub

 

[Peter_SSs]

Try this to delete the sheets

Dim i As Long

Application.DisplayAlerts = False
For i = Sheets.Count To 17 Step -1
  Sheets(i).Delete
Next i
Application.DisplayAlerts = True

 

[Martin_H]

Looking good

Thank you for your time Peter. I appreciate that.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.