How to Delete Last X Sheets in a Workbook

**kirsty** · 12-03-2010, 11:57 PM

Hello, I am wondering if anyone knows how to delete the last X number of sheets from a workbook via VBA? I need a code like this at the end of one of my macros.

Thank you!

**MarvinP** · 12-04-2010, 12:14 AM

Hi kirsty,

When Excel adds a worksheet it gives it an index number. You can see this from the immediate window by typing:

debug.print worksheets.count

and press enter. It will show how many worksheets are in the open workbook.
Then try:

debug.print worksheets(3).name

and press enter. The name of the 3rd (index) will be displayed in the immediate window.

Lets say you have 22 worksheets in a workbook and want to delete that last 10. You could write VBA code like:

For SheetNum = Worksheets.Count To Worksheet.Count - 10 Step -1
    Worksheets(SheetNum).Delete
Next SheetNum

I hope that is enough to have you succeed.

**Greg M** · 12-04-2010, 12:16 AM

Hi Kirsty,

The following code will delete the last three sheets from a workbook which contains this routine:

Option Explicit


Sub DeleteWorksheets()

    Const iNO_OF_SHEETS_TO_DELETE As Integer = 3

    Dim iNoOfSheets As Integer
    Dim wbk         As Workbook

    Set wbk = ThisWorkbook

    iNoOfSheets = wbk.Sheets.Count - iNO_OF_SHEETS_TO_DELETE

    On Error GoTo ErrorEncountered

    Application.DisplayAlerts = False

    Do
        wbk.Sheets(wbk.Sheets.Count).Delete
    Loop Until wbk.Sheets.Count = iNoOfSheets

ExitPoint:

    Application.DisplayAlerts = True

    Exit Sub

ErrorEncountered:

    Resume ExitPoint

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

**Paul** · 12-04-2010, 12:35 AM

And yet a third option for you, Kirsty. It should handle incorrect sheet names or invalid number of sheets.

Sub delSheets()
Dim sh As Worksheet, ans As String, idx As Long

ans = InputBox("Type the first sheet name to delete.  All sheets listed to" & _
    vbCrLf & "the right of that sheet will also be deleted.", "Delete Worksheets")

On Error GoTo notFound
idx = Sheets(ans).Index
On Error GoTo 0

If idx = 1 Then
    MsgBox "You cannot delete all worksheets in a workbook." & vbCrLf & _
    "Please try again and choose another sheet."
    Exit Sub
End If
    
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
    If sh.Index >= idx Then sh.Delete
Next sh
Application.DisplayAlerts = True
Exit Sub

notFound:
MsgBox "There is no sheet named " & ans
End Sub

**kirsty** · 12-04-2010, 12:33 AM

Thank you very much for the responses!

MarvinP - I tried your short code first but it returned an error saying I must define an object? Because I am extremely new at this, I did not attempt to troubleshoot and moved onto the longer code posted by Greg M. Thank you for the information regarding how to obtain worksheet info from Excel; every bit of information helps me a lot to understand what I'm doing.

Greg M - Your code worked perfectly to delete the last two sheets. I can now add it to the end of my macro and it will be nearly perfect. Thank you so much!