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.

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

How To Delete Excel Sheets using VBA Code?

Examples

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.”

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

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.

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.

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.

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

Here is the complete code:

Sub DeleteSheetByName()

    Dim sheetName As String

    sheetName = “Sheet1”

    Worksheets(sheetName).Delete

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.

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

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.

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.

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.

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.

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

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

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.

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

    Worksheets(sheetName).Delete

    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.

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

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

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

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

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.

Here is the full code:

Sub DeleteActiveSheet()

    Dim activeSheet As Worksheet

    Set activeSheet = Sheet5

    activeSheet.Delete

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.

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

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.

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.

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

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

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

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.

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

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.

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

        Worksheets(sheetNames(i)).Delete

        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)

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.

Recommended Articles

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 –

• VBA Delete File
• VBA Delete Row
• VBA Protect Sheet