VBA Delete Sheet

What Is Excel VBA Delete Sheet?

The VBA Delete sheet method is used to remove a sheet using VBA. It prompts the user to confirm the deletion by default when we delete a worksheet. The worksheet in a workbook is deleted when we click the Delete button on the dialog box. Writing code to remove a particular worksheet from a workbook is especially helpful for handling big datasets, automating tedious tasks, and guaranteeing uniform data formatting on several sheets.

Let us look at an example. Here, we’ll write a code that uses the VBA Delete Sheet function to remove worksheet 2. The VBA code below defines a subroutine named “DeleteSheetByIndex.” It uses the “Worksheets” collection, accessed by index, to delete the second sheet in the workbook.

Excel VBA Delete Sheet- Definition 1

The number 2 in Worksheets(2) specifies the index of the sheet to be deleted.

Excel VBA Delete Sheet-Definition 1(1)
Excel VBA Delete Sheet-Definition 1(2)
Key Takeaways
  1. VBA Delete Sheet is used to delete sheets in a workbook with VBA code.
  2. VBA delete sheets can be used by the sheet name, index, or user input, among other criteria. To improve control and customization when deleting sheets, you can also protect sheets or ask users for input.
  3. To avoid runtime errors when deleting non-existent sheets, use error handling (as demonstrated in the above examples).
  4. Before executing your code, make sure to unhide any sheets you wish to keep because VBA can delete hidden sheets also.

How To Delete Excel Sheets using VBA Code?

Deleting Excel sheets using VBA code involves several steps:

  1. Open Excel and press ALT + F11 to open the VBA editor.

    VBA Delete Sheet Step 1

  2. Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

    VBA Delete Sheet Step 2

  3. To remove the desired sheet or sheets, write the VBA code. Sheets can be deleted using a variety of criteria, including name, index, and other criteria.

    Here are a few typical instances:

    How to Delete Excel Sheets using VBA Code - Step 3

  4. Once your VBA code is complete, close the editor and return to your Excel spreadsheet. Then, to run the VBA code, select the necessary macro by pressing “Run” or “Alt + F8” to open the “Macro” dialogue.

  5. Verify that the specified sheets have been deleted from your Excel workbook.


Example #1 – Delete Worksheet by using its Name

In this example, we will use the VBA Delete Sheet function that deletes the worksheet named “Sheet1.”

VBA Delete Sheet-Example-1

Step 1: In the new module, we first start by creating a new subroutine named “DeleteSheetByName.”

VBA Delete Sheet-Example-1-Step-1

Step 2: In this line, we declare a variable “sheetName” as a string. This variable will store the name of the sheet you want to delete.

VBA Delete Sheet-Example-1-Step-2

Step 3: Here, you assign the name of the sheet you want to delete to the “sheetName” variable. You can replace “Sheet1” with the name of the sheet you wish to delete in your code.

VBA Delete Sheet-Example-1-Step-3

Step 4: This line deletes the worksheet with the name specified in the sheetName variable. It uses the Worksheets collection to access the sheet and the “.Delete” method to delete it.

VBA Delete Sheet-Example-1-Step-4

Step 5: Save the macro and click on run. When you run this code, it deletes the worksheet named “Sheet1” in the Excel workbook.

VBA Delete Sheet-Example-1-Step-5

Here is the complete code:

Sub DeleteSheetByName()

    Dim sheetName As String

    sheetName = “Sheet1”


End Sub

Example #2 – Delete Worksheet by its Name with Variables

In this example, we’ll look at how to write a VBA macro that asks the user to name a sheet before attempting to delete it, all the while handling possible errors to ensure a smooth runtime.

VBA Delete Sheet-Example-2

Step 1: This line defines the start of a VBA subroutine named “DeleteSheetByVariable.”

VBA Delete Sheet-Example-2-Step-1

Step 2: Next, we declare a variable “sheetName” as a string, just like we did in the first example. This variable will hold the name of the sheet to be deleted.

VBA Delete Sheet-Example-2-Step-2

Step 3: Here, we display an input box where the user can enter the name of the sheet they want to delete. The entered name is then stored in the sheetName variable.

VBA Delete Sheet-Example-2-Step-3

Step 4: This line starts error handling, which means that any errors that occur in the subsequent code will be ignored, allowing the code to continue running.

VBA Delete Sheet-Example-2-Step-4

Step 5: The worksheet with the name given in the “sheetName” variable is deleted by this line of code. Because of the error handling, an error won’t occur if the sheet doesn’t exist.

VBA Delete Sheet-Example-2-Step-5

Step 6: This line disables error handling. After deleting the sheet, error handling is turned off to handle errors normally.

VBA Delete Sheet-Example-2-Step-6

Step 7: When you run this code, it prompts the user to input a sheet name.

VBA Delete Sheet-Example-2-Step-7

Step 8: It tries to remove the sheet with the specified name when you enter it and click OK, and also handles errors if the sheet doesn’t exist.

VBA Delete Sheet-Example-2-Step-8

Here is the complete code :

Sub DeleteSheetByVariable()

    Dim sheetName As String

    sheetName = InputBox(“Enter the sheet name you want to delete:”)

    On Error Resume Next


    On Error GoTo 0

End Sub

Example #3 – Delete Active Worksheet

In this example, we will learn how to write a VBA macro that will remove the worksheet known as “Sheet5” without requesting confirmation from the user.

VBA Delete Sheet-Example-3

Step 1: In the new module, we first create a VBA subroutine named “DeleteActiveSheet.”

VBA Delete Sheet-Example-3-Step-1

Step 2: Here, we declare a variable “activeSheet” of the Worksheet data type. This variable will hold a reference to the currently active sheet.

VBA Delete Sheet-Example-3-Step-2

Step 3: Now, we will set the “activeSheet” variable to refer to the currently active sheet in the workbook which is “Sheet5.”

VBA Delete Sheet-Example-3-Step-3

Step 4: This line of code deletes the worksheet referenced by the activeSheet variable, which is the currently active sheet.

VBA Delete Sheet-Example-3-Step-4

Step 5: Now, save the macro and click on run. When the code is executed, it will first prompt you to decide whether or not to remove the worksheet. If you choose to delete it, the worksheet in the Excel workbook that is referred to as “Sheet5” will be removed without any confirmation dialogues or alerts.

VBA Delete Sheet-Example-3-Step-5
VBA Delete Sheet-Example-3-Step-5(1)

Here is the full code:

Sub DeleteActiveSheet()

    Dim activeSheet As Worksheet

    Set activeSheet = Sheet5


End Sub

Example #4 – Delete More than One Worksheet

This example demonstrates how to use VBA to delete several worksheets in a loop with the names “Sheet2,” “Sheet3,” and “Sheet4.” Also, error handling is used to ignore any sheets that are not present, ensuring a seamless deletion procedure.

VBA Delete Sheet-Example-4

Step 1: This line defines the start of a VBA subroutine named “DeleteMultipleSheets”.

VBA Delete Sheet-Example-4-Step-1

Step 2: Here, we declare an array variable “sheetNames” to hold the names of sheets to be deleted. It’s an array because you can specify multiple sheet names.

VBA Delete Sheet-Example-4-Step-2

Step 3: This line of code splits the string in VBA “Sheet2, Sheet3, Sheet4” into an array using the comma, as the delimiter. The resulting array contains the names of sheets to be deleted.

VBA Delete Sheet-Example-4-Step-3

Step 4: Now, we declare a counter variable “i” to be used in a loop.

VBA Delete Sheet-Example-4-Step-4

Step 5: This line starts a loop that iterates through the elements of the sheetNames array, from the lower bound to the upper bound.

VBA Delete Sheet-Example-4-Step-5

Step 6: Here, we begin error handling to ignore errors if a sheet specified in the array doesn’t exist.

VBA Delete Sheet-Example-4-Step-6

Step 7: This line deletes the worksheet with the name specified by the current element of the “sheetNames” array. If a sheet doesn’t exist, it won’t trigger an error because of the error handling.

VBA Delete Sheet-Example-4-Step-7

Step 8: Lastly, in order to handle errors in later code normally, this line of code disables error handling following the deletion of sheets.

VBA Delete Sheet-Example-4-Step-8

Step 9: When the entire code is executed, it will remove the “Sheet2,” “Sheet3,” and “Sheet4” worksheets if they are present in the Excel workbook and ignore any that are not because of error handling in VBA.

VBA Delete Sheet-Example-4-Step-9
VBA Delete Sheet-Example-4-Step-9(1)

Here is the complete code:

Sub DeleteMultipleSheets()

    Dim sheetNames() As String

    sheetNames = Split(“Sheet2,Sheet3,Sheet4”, “,”)

    Dim i As Integer

    For i = LBound(sheetNames) To UBound(sheetNames)

        On Error Resume Next


        On Error GoTo 0

    Next i

End Sub

Important Things To Note

  1. VBA Delete Sheet deletes sheets permanently; an “Undo” option is not available. Proceed with caution when utilizing VBA delete sheets.
  2. To prevent mistakes, the VBA Delete Sheet If Exists feature makes sure that the deletion procedure is only carried out when the designated sheet is present.
  3. You can delete a sheet based on whether its name contains particular keywords or phrases by using the VBA Delete Sheet If Name Contains function. You can use this feature to designate which sheets should be deleted based on criteria or naming patterns.
  4. You can use the VBA Delete Sheet Without Warning messages or prompts. By setting the “Application.DisplayAlerts” property to False before deletion, you can execute the deletion process without interruption.

Frequently Asked Questions (FAQs)

1. How to delete a sheet in VBA without the prompt?

Using the “Application.DisplayAlerts” property in VBA, you can delete a sheet without receiving a prompt. To remove confirmation dialogues, set it to False before deleting the sheet and back to True afterward.
Here is an example:
Sub DeleteSheetWithoutPrompt()
    Application.DisplayAlerts = False ‘ Disable confirmation dialog
    Application.DisplayAlerts = True ‘ Enable confirmation dialog
End Sub

2. Can I restore a deleted sheet using VBA?

Unfortunately, there isn’t a straightforward method in VBA for recovering a deleted sheet. To recover lost data, version control or backups are essential.

3. How can I prevent accidental deletion of important sheets using VBA?

Excel has built-in sheet protection features that you can use to stop important sheets from being accidentally deleted. Use the “Worksheet.Protect” method to safeguard the sheets you wish to keep private, and if necessary, provide a password. To remove or alter the password-protected sheets, this will be necessary.

4. What happens if I try to delete a sheet that is protected?

A runtime error will occur if you try to use VBA to delete a protected sheet without first unprotecting it. A protected sheet must first be unprotected using the “Worksheet.Unprotect” method in order to be deleted.

Download Template

This article must help us understand the VBA Delete Sheet formula and examples. You can download the template here to use it instantly.

Guide to VBA Delete Sheet Function in Excel & its meaning. Here we explain how to use the VBA Delete Sheet for cell references, examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *