Deletes every 2nd worksheet

[aaron1000]

Hi Folks, I created code to delete ALL sheets between sheet name 4 and 200....however when executed its only deleting every 2nd worksheet.

I can't see why its skipping every 2nd sheet. Code as follows:

Sub delextraworksheets()
Application.DisplayAlerts = False
For I = 4 To 200
Worksheets(I).Delete
Next I
Application.DisplayAlerts = True
End Sub

Appreciate any help.

cheers
Aaron

 

[p45cal]

change
For I = 4 To 200
to
For I = 200 to 4 step -1

 

[VoG]

You need to go backwards

For i = 200 To 4 Step -1

 

[Sektor]

Sub delextraworksheets()
 Application.DisplayAlerts = False
 For i = 4 To 200 [B][COLOR="Red"]Step 2[/COLOR][/B]
 Worksheets(i).Delete
 Next i
 Application.DisplayAlerts = True
End Sub

 

[Ruddles]

I can't see why its skipping every 2nd sheet.

You start with your pointer at 4.

You delete Sheet(4), so the old Sheet(5) becomes the new Sheet(4) and the old Sheet(6) becomes the new Sheet(5).

You increment your pointer to 5 and it deletes the new Sheet(5) which was the old sheet Sheet(6) before you deleted Sheet(4), so now you've deleted the original Sheet(4) and the original Sheet(6).

Another way of explaining it is that as your pointer increments, the next sheet along is shuffled to the left to replace the sheet you just deleted, and it 'passes' the pointer as it goes in the opposite direction, and it never gets considered as a candidate for deletion.

It's a similar problem to that of deleting rows or columns: you can do it from top-to-bottom or left-to-right, but it's simpler to do it from bottom-to-top or right-to-left.

 

[Sektor]

Sub delextraworksheets()
 Application.DisplayAlerts = False
 [COLOR="Red"]For i = 200 To 4 Step -2[/COLOR]
 Worksheets(i).Delete
 Next i
 Application.DisplayAlerts = True
End Sub

 

[aaron1000]

Ok thanks everyone.

cheers
Aaron