deleting sheets in vba - subscript out of range msg

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,556
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,845
Office Version
365
Platform
Windows
Try:
Code:
[COLOR=#333333]For x = 2 To Worksheets.Count[/COLOR]
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,619
Try:
Code:
Sub delelesheets()
    Application.DisplayAlerts = False
    Do While Worksheets.Count > 1
        Worksheets(Worksheets.Count).Delete
    Loop
    Application.DisplayAlerts = True
End Sub
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
One more code:
Rich (BB 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
 

Forum statistics

Threads
1,086,116
Messages
5,387,923
Members
402,088
Latest member
poppa57

Some videos you may like

This Week's Hot Topics

Top