CODEWITHSUNDEEP
excelvba
Tildm
Tildm

Reputation: 21

Deleting worksheets with looping

Could you tell me someone why just every second sheet is deleted, however if I turn off the worksheets.delete line than in the message box appears all the sheet names.

Sub tor()
    Dim wsz As Integer

    wsz = Application.Worksheets.Count

    For i = 2 To wsz
        MsgBox Application.Worksheets(i).Name
        Application.DisplayAlerts = False
        Application.Worksheets(i).Delete
    Next i
End Sub

Upvotes: 2

Views: 8972

Answers (2)

SBI
SBI

Reputation: 2322

What happens is that when you delete a worksheet from the Worksheets collection, the next sheet takes the index of the one you just deleted. You then increment i, skipping the next sheet that you actually wanted to delete.

The easiest solution is to delete worksheets beginning from the end.

For i = ThisWorkbook.Worksheets.Count To 2 Step -1
    Application.Worksheets(i).Delete
Next i

Upvotes: 4

Siddharth Rout
Siddharth Rout

Reputation: 149335

Delete the sheet in reverse. Every time you delete a sheet, the Sheet index gets shifted.

Sub tor()
    Dim wsz As Integer, i as Long

    wsz = ThisWorkbook.Worksheets.Count

    Application.DisplayAlerts = False

    For i = wsz To 2 Step -1
        ThisWorkbook.Worksheets(i).Delete
    Next i

    Application.DisplayAlerts = True
End Sub

Upvotes: 3

Related Questions