Delete Sheet in Excel Using VBA

Managing worksheets is a common task for Excel users, and VBA can simplify this process.

In this article, I will show you different ways you can use VBA codes to delete sheets in Excel.

Delete a Specific Sheet by Name

Sub DeleteSheetByName()

    ' Deletes the workheet with the name Sheet1
    ThisWorkbook.Worksheets("Sheet1").Delete

End Sub

In the above code, we have used the line ThisWorkbook.Worksheets(“Sheet1”).Delete where:

  • ThisWorkbook – refers to the workbook in which the code is run.
  • Worksheets(“Sheet1”) – refers to the worksheet you want to delete (Sheet1 in this case)
  • .Delete – The delete method of the worksheet object is used to delete the sheet

When you run the above code, Excel will show you a warning before deleting the sheet (as shown below).

Excel prompt before deleting the sheet permanently

If you click on the delete button, the sheet will be deleted. If you click the Cancel button, the sheet will not be deleted.

There should always be at least one sheet in the workbook. If you try to delete the last sheet in the workbook, it will give you a Run time error (1004)

Sheet vs. Worksheet (Important Note)

In VBA, the terms “Sheet” and “Worksheet” are often used interchangeably, but they are not the same.

  • Worksheet: A worksheet refers to what we commonly understand as an Excel sheet with rows and columns where we can manipulate data. When we say “Worksheet” in VBA, we are exclusively talking about these types of sheets. The “Worksheet” object in VBA belongs to the Worksheets collection.
  • Sheet: A “Sheet” can refer to any type of sheet in an Excel workbook, which includes Worksheets, Chart Sheets, and other types of specialty sheets (like Excel 4.0 Macro sheets). The “Sheet” object in VBA belongs to the Sheets collection and is more generic.

In most cases, it’s alright to use the terms Sheet and Worksheet interchangeably, as most Excel users only work with worksheets. But it’s important to know the difference.

Also read: VBA Clear Sheet

Delete Sheet by Index Number

Apart from using the worksheet name, you can also delete a sheet using VBA by specifying its index number. The index number refers to the position of the sheet in the workbook.

For example, ThisWorkbook.Worksheets(1) refers to the first worksheet in the workbook where the code is run. ThisWorkbook.Worksheets(2) refers to the second workbook and so on.

Below is the VBA code that would delete the first worksheet in the workbook:

Sub DeleteSheetByName()

    ' Deletes the first worksheet in the worksbook
    ThisWorkbook.Worksheets(1).Delete

End Sub

Delete Sheet without Prompt / Warning

If you use any of the above VBA codes, it is going to show you a warning prompt before deleting the sheet.

This is because deleting a sheet is a big task, and Excel doesn’t want VBA to delete a sheet without giving you a chance to review it first.

In case you don’t want to see that prompt when trying to delete a sheet using the VBA code, you can use the code below:

Sub DeleteSheetWithoutPrompt()

    ' Set the display alerts to False, so no prompts are shown
    Application.DisplayAlerts = False
    
    ' Deletes the workheet with the name Sheet1
    ThisWorkbook.Worksheets("Sheet1").Delete
    
    ' Set the display alerts back to to True
    Application.DisplayAlerts = True

End Sub

In the above code, I used the line Application.DisplayAlerts = False before deleting the sheet.

This ensures that I don’t see any prompt or warning while the sheet is being deleted. Once the sheet deletion is done, I set the Application.DisplayAlerts value back to True (which is the default setting).

Remember that the change is done by a VBA code or irreversible. So, if you delete a sheet, you can’t undo it later. So, make sure to have a backup before running this code.

Delete All Sheets Except the Active Sheet

Sub DeleteAllSheetsExceptActive()
    Dim ws As Worksheet
    Dim activeWsName As String
    
    ' Store the name of the active sheet in variable
    activeWsName = ActiveSheet.Name
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets

        ' Check if worksheet name is different that active worksheet name
        If ws.Name <> activeWsName Then

            ' Delete the worksheet without displaying a confirmation message
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True

        End If
    Next ws
End Sub

In the above code, we first save the name of the active worksheet in a variable activeWsName.

It then loops through all the worksheets in the workbook and checks the name of each worksheet. If the name of the worksheet is not the same as that stored in the variable, it is deleted.

Also read: Rename Sheet Using VBA in Excel

Delete Sheets If Names Contains Specific Text

When managing workbooks that contain a lot of worksheets, you may sometimes want to delete all the sheets that contain a specific text or string in its name.

Below is the VBA code that would delete all the worksheets that have the term “Sales” in its name:

Sub DeleteSheetsContainingText()
    Dim ws As Worksheet
    Dim wsName As String
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets

        ' Check if the worksheet name contains the word "sales"
        If InStr(1, ws.Name, "sales", vbTextCompare) > 0 Then
        
            ' Delete the worksheet without displaying a confirmation message
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
            
        End If
    Next ws
End Sub

The above code goes through each worksheet in the workbook and checks whether it contains the text “Sales” or not. This is done using the VBA INSTR function.

If the name contains the word “Sales”, that worksheet is deleted.

Remember that Excel would not allow you to delete all the worksheets in a workbook. So if all the sheets contain the text ‘sales’, you will see an error before the last one is deleted.

Also read: VBA to Hide Sheets

Delete Sheets Based on Cell Value

Sub DeleteSheetIfCellContains()
    Dim ws As Worksheet
    
    ' Loop through each worksheet in the workbook
    For Each ws In Worksheets

        ' Check if the value in cell A1 in the sheet is Sales
        If ws.Range("A1").Value = "Sales" Then
        
            ' Delete the worksheet without displaying a confirmation message
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
            
        End If
    Next ws
End Sub

In the above code, we look through all the worksheets and check the value in cell A1 for each sheet. If the value in cell A1 is “Sales”, that sheet is deleted, else nothing happens.

Delete Every Other Sheet (Delete Alternate Sheets)

Sub DeleteAlternateSheets()
    Dim i As Integer
    
    ' Loop through worksheets in reverse order using step 2 for alternates
    For i = ThisWorkbook.Sheets.Count To 1 Step -2
        ' Delete the worksheet without displaying a confirmation message
        Application.DisplayAlerts = False
        ThisWorkbook.Sheets(i).Delete
        Application.DisplayAlerts = True
    Next i
End Sub

The above VBA code loops through all the worksheets in the workbook in reverse order, stepping by 2 to select alternate sheets.

Each selected sheet is then deleted without displaying a confirmation message.

Delete the Sheet if it Exists

Sub DeleteSheetIfExists()
    Dim ws As Worksheet
    Dim sheetName As String
    Dim sheetExists As Boolean
    
    ' Set sheetName variable to the sheet name
     sheetName = "Sheet1"

    ' Initialize variable to false
    sheetExists = False
    
    ' Loop through each worksheet to check if the sheet exists
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = sheetName Then
            
            Application.DisplayAlerts = False
            ThisWorkbook.Sheets(sheetName).Delete
            Application.DisplayAlerts = True

            Exit For
        End If
    Next ws

End Sub

The above code uses a For Next loop to check whether the sheet with the given name exists or not.

As soon as it finds the sheet that has the name, it gets deleted.

Note: In case the workbook does not contain the sheet with the specified name, the above code goes through the loop and ends without any prompt. If you want, you can use a message box to show a prompt in case the sheet is not found

Delete Sheet After or Before a Specific Sheet

Sub DeleteSheetAfterSales()
    Dim i As Integer
    Dim sheetIndex As Integer
    Dim sheetExists As Boolean
    
    ' Initialize variable to false
    sheetExists = False
    
    ' Loop through worksheets to find the index of the sheet named "Sales"
    For i = 1 To ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(i).Name = "Sales" Then
            sheetExists = True
            sheetIndex = i
            Exit For
        End If
    Next i
    
    ' If "Sales" sheet exists and is not the last sheet, delete the sheet after it
    If sheetExists And sheetIndex < ThisWorkbook.Sheets.Count Then
        Application.DisplayAlerts = False
        ThisWorkbook.Sheets(sheetIndex + 1).Delete
        Application.DisplayAlerts = True
        MsgBox "Deleted Sheet with name: " & Sheets(i).Name
    End If
End Sub

The above VBA subroutine looks for a sheet named “Sales” in the workbook.

If it finds the sheet and if it’s not the last sheet in the workbook, the subroutine deletes the sheet immediately following “Sales” without any confirmation. It also shows a message box showing the name of the sheet it deleted.

So if you don’t see the message box, you can assume that it either didn’t find the sheet or the Sales sheet was the last sheet, so it could not have deleted the sheet after that.

You can modify the code to delete a sheet before a specific sheet.

Delete All Hidden Sheets

Sub DeleteAllHiddenSheets()
    Dim ws As Worksheet
    
    ' Loop through each worksheet in the workbook using a backward loop
    For Each ws In Worksheets
        
        ' Check if the worksheet is hidden
        If ws.Visible <> xlSheetVisible Then
            ' Delete the worksheet without displaying a confirmation message
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws
End Sub

The above code loops through all the worksheets in the workbook and checks the visible property of the sheet.

If the visible property value is not equal to xlSheetVisible, it means that the sheet is hidden and is deleted.

Also read: VBA Create New Sheet (Sheets.Add)

Delete All Chart Sheets

As I mentioned earlier in this article, a workbook can contain worksheets as well as other sheet objects such as a Chart sheet or a Macro 4 worksheet.

Below is the VBA code that would delete all the chart sheets in your workbook (without doing anything to the worksheets).

Sub DeleteAllChartSheets()
    Dim i As Integer
    
    ' Loop through each sheet in the workbook using a backward loop
    For i = ThisWorkbook.Sheets.Count To 1 Step -1
        ' Check if the sheet is a chart sheet
        If TypeName(ThisWorkbook.Sheets(i)) = "Chart" Then
            ' Delete the chart sheet without displaying a confirmation message
            Application.DisplayAlerts = False
            ThisWorkbook.Sheets(i).Delete
            Application.DisplayAlerts = True
        End If
    Next i
End Sub

This code loops through all chart sheets in the workbook in reverse order. We do this to avoid issues with changing indices as sheets are deleted.

The code checks the type of the sheet using the TYPENAME function. If the sheet type is Chart, it is deleted.

Other articles you may also like:

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.
Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster