Code for deleting worksheets

[Giordano Bruno]

I have a workbook which creates 52 worksheets each year. At the end of the year, I wish to delete them. They are all contiguous and typically occupy sheet index numbers 11 to 62 or 12 to 63. Can anyone suggest an efficient code to delete them all. I know it's only once a year, but I don't want to have to make a progress bar. Is it simply best to work through them and delete them one at a time or could I group them by adding them to a collection or an expanding array and then delete the lot in one go. Is that even possible? Also, I don't want to move the remaining worksheets to a new workbook as I don't know what links someone might add during the year. Any suggestions will be gratefully received.

 

[Misca]

If you want to delete them by the index numbers try something like:

Application.DisplayAlerts = False
    
    Sheets(Array(2, 3, 4, 5)).Delete
    
Application.DisplayAlerts = True

If it's always the last x sheets you want to delete try something like:

Dim i As Integer
Dim First As Integer
Dim Last As Integer

Last = Worksheets.Count
First = Last - 5    'Deletes the last 5 sheets

Application.DisplayAlerts = False
    
For i = First To Last
    Sheets(First).Delete
Next i
    
Application.DisplayAlerts = True

 

[Giordano Bruno]

Hi Misca,
Many thanks for your very detailed reply. I'll experiment with them both. I can make it a rule that no sheets may be added on the right hand side and it should not be a problem after a few weeks as that end of the workbook will not be so tempting for people to use anyway. I'm hoping that the Array, albeit with 52 members, will be the faster method as loops always seem to be so slow.

 

[Giordano Bruno]

Hi Misca,
That was interesting, I have tried them both, removing 52 worksheets, and to the nearest second, they both came in as instantaneous. Not what I was expecting at all. Thanks again for your help.