• Login
  • Courses
  • Books
  • Cheat Sheets
  • Tutorials Archive
  • VBA Code Generator
  • Contact

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Delete Sheet: Step-by-Step Guide and 6 Examples to Delete Sheets with Macros

Excel VBA Delete Sheet: Step-by-Step Guide and 6 Examples to Delete Sheets with Macros - FeaturedIn this VBA Tutorial, you learn how to delete sheets in Excel (in different ways) with macros.

This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

Table of Contents

  • Related VBA and Macro Tutorials
  • #1: Delete Sheet by Position
    • VBA Code to Delete Sheet by Position
    • Process Followed by VBA Code to Delete Sheet by Position
    • VBA Statement Explanation
    • Macro Example to Delete Sheet by Position
    • Effects of Executing Macro Example to Delete Sheet by Position
  • #2: Delete Active Sheet
    • VBA Code to Delete Active Sheet
    • Process Followed by VBA Code to Delete Active Sheet
    • VBA Statement Explanation
    • Macro Example to Delete Active Sheet
    • Effects of Executing Macro Example to Delete Active Sheet
  • #3: Delete Sheet by Name
    • VBA Code to Delete Sheet by Name
    • Process Followed by VBA Code to Delete Sheet by Name
    • VBA Statement Explanation
    • Macro Example to Delete Sheet by Name
    • Effects of Executing Macro Example to Delete Sheet by Name
  • #4: Delete Sheet Without Prompt or Warning
    • VBA Code to Delete Sheet Without Prompt or Warning
    • Process Followed by VBA Code to Delete Sheet Without Prompt or Warning
    • VBA Statement Explanation
    • Macro Example to Delete Sheet Without Prompt or Warning
    • Effects of Executing Macro Example to Delete Sheet Without Prompt or Warning
  • #5: Delete Sheet if it Exists
    • VBA Code to Delete Sheet if it Exists
    • Process Followed by VBA Code to Delete Sheet if it Exists
    • VBA Statement Explanation
    • Macro Example to Delete Sheet if it Exists
    • Effects of Executing Macro Example to Delete Sheet if it Exists
  • #6: Delete Multiple Sheets
    • VBA Code to Delete Multiple Sheets
    • Process Followed by VBA Code to Delete Multiple Sheets
    • VBA Statement Explanation
    • Macro Example to Delete Multiple Sheets
    • Effects of Executing Macro Example to Delete Multiple Sheets
  • References to VBA Constructs Used in this VBA Tutorial

Related VBA and Macro Tutorials

The following VBA and macro tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Learn about commonly-used VBA terms here.

    • Learn how to work with the Visual Basic Editor (VBE) here.

    • Learn how to create and work with VBA Sub procedures here.

    • Learn how to work with object methods here.

    • Learn how to declare and work with variables here.

    • Learn how to work with data types here.

    • Learn how to work with arrays here.

  • Practical VBA applications and macro examples:

    • Learn other operations you can carry out when working with Excel worksheets here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Delete Sheet by Position

VBA Code to Delete Sheet by Position

To delete a sheet by its position using VBA, use a statement with the following structure:

Sheets(SheetIndex).Delete

Process Followed by VBA Code to Delete Sheet by Position

Identify sheet to delete by position > delete sheet

VBA Statement Explanation

  1. Item: Sheets.

    • VBA Construct: Workbook.Sheets property.

    • Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you deal with. Identify a single object from this Sheets collection by specifying the appropriate index number (SheetIndex).

      When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you deal with.

      When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you deal with.

  2. Item: SheetIndex.

    • VBA Construct: Index parameter/number of the sheet you want to delete.

    • Description: The Index parameter/number of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

      The Index parameter/number represents the position of the sheet, worksheet or chart sheet in the tab bar of the workbook you deal with, from left to right. For example, 1 is the first (leftmost) sheet/worksheet/chart sheet.

      When specifying the Index parameter/number, consider the following:

      • The count usually includes hidden sheets/worksheets/chart sheets as well.

      • If you're working with the Workbook.Worksheets property, the count includes worksheets but not chart sheets.

      • If you're working with the Workbook.Charts property, the count includes chart sheets but not worksheets.

      If you explicitly declare a variable to represent SheetIndex, use the Long data type.

  3. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetIndex).

      When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.

Macro Example to Delete Sheet by Position

The following macro deletes the first sheet (Sheets(mySheetIndex)) in the workbook where the macro is stored (ThisWorkbook). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'declare variable to hold index number of sheet you want to delete
    Dim mySheetIndex As Long

    'specify index number of sheet you want to delete
    mySheetIndex = 1

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify sheet you want to delete, and delete it
    ThisWorkbook.Sheets(mySheetIndex).Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub

Effects of Executing Macro Example to Delete Sheet by Position

The following GIF illustrates the results of executing the macro example. The first sheet in the workbook (Sheet1) is deleted.

Macro deletes sheet by position

#2: Delete Active Sheet

VBA Code to Delete Active Sheet

To delete the active sheet with VBA, use a statement with the following structure:

ActiveSheet.Delete

Process Followed by VBA Code to Delete Active Sheet

Identify active sheet > delete sheet

VBA Statement Explanation

  1. Item: ActiveSheet.

    • VBA Construct: Application.ActiveSheet property.

    • Description: The Application.ActiveSheet property returns an object representing the active sheet.

  2. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) returned by ActiveSheet (the active sheet).

      When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.

Macro Example to Delete Active Sheet

The following macro deletes the active sheet (ActiveSheet). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteActiveSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify active sheet, and delete it
    ActiveSheet.Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub

Effects of Executing Macro Example to Delete Active Sheet

The following GIF illustrates the results of executing the macro example. The active sheet (Sheet2) is deleted.

Macro deletes active sheet

#3: Delete Sheet by Name

VBA Code to Delete Sheet by Name

To delete a sheet by name using VBA, use a statement with the following structure:

Sheets(SheetName).Delete

Process Followed by VBA Code to Delete Sheet by Name

Identify sheet to delete by name > delete sheet

VBA Statement Explanation

  1. Item: Sheets.

    • VBA Construct: Workbook.Sheets property.

    • Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you deal with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

      When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you deal with.

      When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you deal with.

  2. Item: SheetName.

    • VBA Construct: Name of the sheet you want to delete.

    • Description: The name of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

      For these purposes, the sheet name is that displayed in the tab of the worksheet or chart sheet. If you explicitly declare a variable to represent SheetName, use the String data type.

  3. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetName).

      When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.

Macro Example to Delete Sheet by Name

The following macro deletes the sheet named “delete Sheet” (Sheets(mySheetName)) in the workbook where the macro is stored (ThisWorkbook). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteSheetByName()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'declare variable to hold name of sheet you want to delete
    Dim mySheetName As String

    'specify name of sheet you want to delete
    mySheetName = "delete sheet"

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify sheet you want to delete, and delete it
    ThisWorkbook.Sheets(mySheetName).Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub

Effects of Executing Macro Example to Delete Sheet by Name

The following GIF illustrates the results of executing the macro example. The sheet named “delete sheet” is deleted.

Macro deletes sheet by name

#4: Delete Sheet Without Prompt or Warning

VBA Code to Delete Sheet Without Prompt or Warning

To delete a sheet without Excel displaying the usual prompt (warning) with VBA, use a macro with the following statement structure:

Application.DisplayAlerts = False
Sheets(SheetName).Delete
Application.DisplayAlerts = True

Process Followed by VBA Code to Delete Sheet Without Prompt or Warning

Suppress prompts > identify sheet to delete > delete sheet > enable prompts

VBA Statement Explanation

Lines #1 and #3: Application.DisplayAlerts = False | Application.DisplayAlerts = True

  1. Item: Application.DisplayAlerts.

    • VBA Construct: Application.DisplayAlerts property.

    • Description: The Application.DisplayAlerts property allows you to specify whether Excel displays alerts and messages while the macro is running. When you delete a sheet, the main alert that Excel usually displays (and you want to handle) is the dialog box that prompts the user to confirm the sheet deletion.

      Microsoft Excel will permanently delete this sheet. Do you want to continue?

      The default value of the Application.DisplayAlerts property is True. In such cases, Excel displays the dialog box prompting the user to confirm the sheet deletion.

  2. Item: False.

    • VBA Construct: New property value of Application.DisplayAlerts property.

    • Description: When you delete a sheet, you can suppress the dialog box that prompts the user to confirm the sheet deletion, by setting the Application.DisplayAlerts property to False.

      When you set Application.DisplayAlerts to False and Excel requires a response, Excel chooses the default response. When you delete a sheet, the default response to the dialog box prompting the user to confirm the sheet deletion is “Delete”. This results in Excel deleting the sheet.

  3. Item: True.

    • VBA Construct: New property value of Application.DisplayAlerts property.

    • Description: The default value of the Application.DisplayAlerts property is True. This results in Excel displaying alerts and messages while a macro is running.

      Generally, when you set Application.DisplayAlerts to False, Excel sets the property back to True upon finishing macro execution. Since there are exceptions (such as executing cross-process code), you can explicitly set Application.DisplayAlerts back to True after deleting the sheet.

Line #2: Sheets(SheetName).Delete

  1. Item: Sheets.

    • VBA Construct: Workbook.Sheets property.

    • Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you deal with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

      When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you deal with.

      When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you deal with.

  2. Item: SheetName.

    • VBA Construct: Name of the sheet you want to delete.

    • Description: The name of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

      For these purposes, the sheet name is that displayed in the tab of the worksheet or chart sheet. If you explicitly declare a variable to represent SheetName, use the String data type.

  3. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetName).

Macro Example to Delete Sheet Without Prompt or Warning

The following macro deletes the sheet named “delete sheet no prompt” (Sheets(mySheetName)) in the workbook where the macro is stored (ThisWorkbook) without displaying the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteSheetNoPrompt()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'declare variable to hold name of sheet you want to delete
    Dim mySheetName As String

    'specify name of sheet you want to delete
    mySheetName = "delete sheet no prompt"

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify sheet you want to delete, and delete it
    ThisWorkbook.Sheets(mySheetName).Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub

Effects of Executing Macro Example to Delete Sheet Without Prompt or Warning

The following GIF illustrates the results of executing the macro example. The sheet named “delete sheet no prompt” is deleted without a prompt or warning.

Macro deletes sheet with no prompt

#5: Delete Sheet if it Exists

VBA Code to Delete Sheet if it Exists

To delete a sheet if it exists with VBA, use a macro with the following statement structure:

On Error Resume Next
Sheets(SheetName).Delete
On Error GoTo 0

Process Followed by VBA Code to Delete Sheet if it Exists

Enable error handling > identify sheet to delete > delete sheet if it exists > disable error handling

VBA Statement Explanation

Line #1: On Error Resume Next

  1. Item: On Error Resume Next.

    • VBA Construct: On Error Resume Next statement.

    • Description: The On Error Resume Next statement specifies that, if an error occurs, control goes to the statement immediately following the statement where the error occurs. Execution continues at that statement that follows that where the error occurs.

      “Sheets(SheetName).Delete” usually returns run-time error 9 (subscript out of range) when the sheet identified by Sheets(SheetName) doesn't exist. Without the On Error Resume Next statement, such error results in Excel displaying an error message and stopping macro execution.

      Therefore:

      • If the sheet named SheetName exists, Excel deletes the sheet as specified by “Sheets(SheetName).Delete”.

      • If the sheet named SheetName doesn't exist, execution of the macro continues on the statement following “Sheet(SheetName).Delete”. Due to the On Error Resume Next statement, Excel handles the run-time error.

Line #2: Sheets(SheetName).Delete

  1. Item: Sheets.

    • VBA Construct: Workbook.Sheets property.

    • Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you deal with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

      When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you deal with.

      When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you deal with.

  2. Item: SheetName.

    • VBA Construct: Name of the sheet you want to delete.

    • Description: The name of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

      For these purposes, the sheet name is that displayed in the tab of the worksheet or chart sheet. If you explicitly declare a variable to represent SheetName, use the String data type.

  3. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetName).

      When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section above to delete a sheet with no prompt.

Line #3: On Error GoTo 0

  1. Item: On Error GoTo 0.

    • VBA Construct: On Error GoTo 0 statement.

    • Description: The On Error GoTo 0 statement disables the error handling specified by the On Error Resume Next statement. If you omit the On Error GoTo 0 statement, Excel generally disables the error handler automatically when exiting the procedure.

Macro Example to Delete Sheet if it Exists

The following macro deletes the sheet named “delete sheet if exists” (Sheets(mySheetName)) in the workbook where the macro is stored (ThisWorkbook), if such sheet exists. If the sheet doesn't exist, the macro handles the error. The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteSheetIfExists()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'declare variable to hold name of sheet you want to delete
    Dim mySheetName As String

    'specify name of sheet you want to delete
    mySheetName = "delete sheet if exists"

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'enable error handling to deal with error that occurs when (if) you try to delete a sheet that doesn't exist
    On Error Resume Next

    'identify sheet you want to delete, and delete it
    ThisWorkbook.Sheets(mySheetName).Delete

    'disable error handling
    On Error GoTo 0

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub

Effects of Executing Macro Example to Delete Sheet if it Exists

The following GIF illustrates the results of executing the macro example. The sheet named “delete sheet if exists” is deleted.

Macro deletes sheet if it exists

#6: Delete Multiple Sheets

VBA Code to Delete Multiple Sheets

To delete multiple sheets with VBA, use a statement with the following structure:

Sheets(Array(SheetsList)).Delete

Process Followed by VBA Code to Delete Multiple Sheets

Identify multiple sheets to delete as an array > delete sheets

VBA Statement Explanation

  1. Item: Sheets.

    • VBA Construct: Workbook.Sheets property.

    • Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you deal with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

      When deleting worksheets, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you deal with.

      When deleting chart sheets, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you deal with.

  2. Item: Array(…).

    • VBA Construct: Array function.

    • Description: The Array function returns a Variant containing an array.

  3. Item: SheetsList.

    • VBA Construct: Argument list of Array function.

    • Description: A comma-delimited list of the values you assign to each of the array elements.

      You can generally identify specific objects from the Sheets collection you work with using an index number or the sheet name, as follows:

      • The index number represents the position of a sheet, worksheet or chart sheet in the tab bar of the workbook you deal with, from left to right. For example, 1 is the first (leftmost) sheet/worksheet/chart sheet.

        When specifying the Index parameter/number, consider the following:

        • The count usually includes hidden sheets/worksheets/chart sheets as well.

        • If you're working with the Workbook.Worksheets property, the count includes worksheets but not chart sheets.

        • If you're working with the Workbook.Charts property, the count includes chart sheets but not worksheets.

      • The sheet name is that displayed in the tab of the worksheet or chart sheet.

  4. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheets or chart sheets) you identify with Sheets(Array(SheetsList)).

      When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section above to delete a sheet with no prompt.

Macro Example to Delete Multiple Sheets

The following macro deletes (i) the first 2 sheets, and (ii) the sheets named “delete multiple sheets 1” and “delete multiple sheets 2” (Sheets(mySheetNames)), in the workbook where the macro is stored (ThisWorkbook). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteMultipleSheets()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'declare variable to hold Variant containing array whose elements are the index numbers or names of the sheets you want to delete
    Dim mySheetNames() As Variant

    'specify (using index numbers or names) the sheets you want to delete
    mySheetNames = Array(1, 2, "delete multiple sheets 1", "delete multiple sheets 2")

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify sheets you want to delete, and delete them
    ThisWorkbook.Sheets(mySheetNames).Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub

Effects of Executing Macro Example to Delete Multiple Sheets

The following GIF illustrates the results of executing the macro example. The (i) the first 2 sheets (Sheet6 and Sheet7), and (ii) the sheets named “delete multiple sheets 1” and “delete multiple sheets 2”, are deleted.

Macro deletes multiple sheets

References to VBA Constructs Used in this VBA Tutorial

Use the following links to visit the appropriate webpage in the Microsoft Developer Network:

  1. Identify workbook containing sheets, worksheets or chart sheets to delete:

    • Application.ThisWorkbook property.

    • Application.ActiveWorkbook property.

    • Application.Workbooks property.

    • Workbook object.

  2. Identify sheets, worksheets and chart sheets:

    • Workbook.Sheets property.

    • Application.ActiveSheet property.

    • Sheets object.

    • Workbook.Worksheets property.

    • Worksheet object.

    • Workbook.Charts property.

    • Chart object.

  3. Delete sheets, worksheets and chart sheets:

    1. Worksheet.Delete method.

    2. Chart.Delete method.

  4. Prevent Excel from displaying prompts or warnings when deleting a sheet, worksheet or chart sheet:

    • Application.DisplayAlerts property.

  5. Handle errors:

    • On Error statement.

  6. Create an array containing the names of several sheets, worksheets or chart sheets to delete:

    • Array function.

  7. Work with variables and data types:

    • Dim statement.

    • = operator.

    • Data types:

      • Long data type.

      • String data type.

      • Variant data type.

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure

Copyright © 2015–2023 PDS Intelligence Pte. Ltd. All rights reserved.
Excel ® is a registered trademark of the Microsoft Corporation. Power Spreadsheets is not affiliated with the Microsoft Corporation.