deleting sheets in vba - subscript out of range msg

[lezawang]

Hi
I want to delete all the sheets in a workbook except the first one. I wrote the code below but what is the problem, It deleted some but then I got message "subscript out of range". I think because I do not have 40 sheets (my For loop is from 2 to 40). I do not know how many sheets in the file. I want to delete them no matter how many. Is that possible? thank you.


Sub delelesheets()
Dim x As Integer
For x = 2 To 4
ThisWorkbook.Worksheets(x).Delete
Next
End Sub

 

[Joe4]

Try:

[COLOR=#333333]For x = 2 To Worksheets.Count[/COLOR]

 

[mumps]

Try:

Sub delelesheets()
    Application.DisplayAlerts = False
    Do While Worksheets.Count > 1
        Worksheets(Worksheets.Count).Delete
    Loop
    Application.DisplayAlerts = True
End Sub

 

[ZVI]

One more code:

Sub DeleteExtraSheets()
  If Sheets.Count = 1 Then Exit Sub
  Application.DisplayAlerts = False
  Sheets(Evaluate("TRANSPOSE(ROW(2:" & Sheets.Count & "))")).Delete
  Application.DisplayAlerts = True
End Sub