Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel VBA: Delete Sheet If It Exists (4 Methods)

In this article, we’ll illustrate how to delete a sheet if exists from a workbook using VBA in Excel with 4 examples. As deleting worksheets is a common task in Excel, we should know how to delete sheets by defining the sheet names in the VBA code just like we can delete them using the keyboard’s Delete key. Let’s dive into the examples to get a clear understanding of the VBA codes to use.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Methods to Delete Sheet If Exists Using VBA in Excel

To illustrate how to delete single or multiple sheets if exist, we’re going to apply different methods with VBA code. We’ll use the following command to delete a sheet using VBA code.

Worksheets(“Worksheet Name”).delete

In this command, we call the Worksheet.Delete method of excel that deletes a Worksheet object. We need to put the worksheet name like Sheet1, Sheet2, etc inside the quotation mark.

Delete Sheet If Exists Using VBA in Excel

Let’s say we want to delete a worksheet named “Sheet5” from the workbook in the above screenshot.

The code in the visual basic editor would be-

Sub DeleteSheet()
Worksheets("Sheet5").delete
End Sub

It would return an error message as we don’t have a worksheet named “Sheet5”.

In the following examples, we’re going to use the If…Then…Else statement to check whether the worksheet name exists or not. If exists it will apply the Worksheets(“Worksheet Name”).delete command. By using the For…Next statement, we’ll check the existence of the worksheet name through all the worksheets of the workbook. Finally, if the worksheet doesn’t exist, the code is configured to show a message using the MsgBox function. Let’s dive into the examples with the required VBA code to run.

Write Code in Visual Basic Editor

Follow the steps to open the visual basic editor and write some code there.

  • Go to the Developer tab from the Excel Ribbon.
  • Click the Visual Basic option.

  • In the Visual Basic For Applications window, click the Insert dropdown to select the New Module.

Now that a new module is opened, write some code there and press F5 to run.


1. Delete a Sheet by Its Name If Exists Using VBA in Excel

In this example, we’ll try to delete a sheet by its name by checking first whether it exists or not. Let’s say we want to delete the worksheet named “Sheet1”. We need to follow the steps below.

  • In the visual basic editor copy and paste the following code.
Sub DeleteSheetIfExists()
Dim check As Boolean
For Each Sheet In Worksheets
If Sheet.Name Like "Sheet1" Then check = True: Exit For
Next
If check = True Then
Worksheets("Sheet1").delete
Else
MsgBox "Worksheet Doesn't Exist"
End If
End Sub

Delete Sheet If Exists Using VBA in Excel

  • Now press F5 to run the code.
  • Click the Delete button to delete the worksheet from the workbook.

Delete Sheet If Exists Using VBA in Excel

  • As a result, we successfully deleted the worksheet named “Sheet1”.

If Worksheet Doesn’t Exist:

Now let’s check what happens if we run the code again in the visual basic editor.

Delete Sheet If Exists Using VBA in Excel

There is a Msg Box popped up with the message that we wrote in the code– ” Worksheet Doesn’t Exist.” as the Sheet1 no more exists in the workbook.

Delete Sheet If Exists Using VBA in Excel

Read More: How to Delete Multiple Sheets in Excel (4 Ways)


2. Use of VBA Code to Delete a Sheet If Exists Without Alert Message

In this illustration, we want to get rid of the alert message that the Worksheet.Delete method shows the user to confirm the deletion. For this, we need to set the Application.DisplayAlerts property to False inside the code. Now, let’s apply the following code to accomplish this.

Sub DeleteSheetIfExists()
Dim check As Boolean
For Each Sheet In Worksheets
If Sheet.Name Like "Sheet2" Then check = True: Exit For
Next
If check = True Then
    Application.DisplayAlerts = False
Worksheets("Sheet2").delete
 Application.DisplayAlerts = True
Else
MsgBox "Worksheet Doesn't Exist"
End If
End Sub

This code directly deleted the worksheet named “Sheet2without any alert message.

Read More: How to Delete Excel Sheet Using VBA (10 Suitable Ways)


Similar Readings:


3. Delete a Sheet If Exists Based on User Input Using VBA in Excel

This example shows how to take the worksheet name as a user input rather than hardcode it in the VBA code to delete a sheet if it exists in Excel. We need to use Excel’s InputBox function and configure it so that it takes a string value as an input. For this, we declared a variable named SheetName as String and configure the InputBox’s type property to 2 which takes string inputs. Follow the steps below to see this working.

  • Put the following code inside the visual code editor.
Sub DeleteSheetIfExists()
Dim check As Boolean
 Dim SheetName As String
    SheetName = Application.InputBox( _
      Prompt:="Put the Sheet Name you want to delete", _
      Type:=2)
For Each Sheet In Worksheets
If Sheet.Name Like SheetName Then check = True: Exit For
Next
If check = True Then
    Application.DisplayAlerts = False
Worksheets(SheetName).delete
 Application.DisplayAlerts = True
Else
MsgBox "Worksheet Doesn't Exist"
End If
End Sub
  • Press F5 to run the code.
  • In the input box put the worksheet name (in this example, Sheet3) and hit OK.

Delete Sheet If Exists Using VBA in Excel

  • We’ve successfully deleted the Sheet3 worksheet.

If Worksheet Doesn’t Exist:

Now let’s check what happens if we run the code again and put Sheet3 as the input in the input box.

Delete Sheet If Exists Using VBA in Excel

There is a Msg Box popped up with the message that we wrote in the code-” Worksheet Doesn’t Exist.” as the Sheet3 no more exists in the workbook.

Delete Sheet If Exists Using VBA in Excel

Related Content: Shortcut to Delete Sheet in Excel (5 Quick Examples)


4. Run a VBA Code to Delete Multiple Sheets If Exists Based on User Input in Excel

In this example, we modified our VBA code so that we can delete multiple sheets from a workbook based on user input. Copy and paste the following code and press F5 to run it.

Sub DeleteMulltipleSheets()
  Dim SheetName As String
For Each Sheet In ActiveWorkbook.Worksheets
    SheetName = Application.InputBox( _
      Prompt:="Put the Sheet Name you want to delete, left Blank to exit ", _
      Type:=2)
    If Sheet.Name = SheetName Then
Application.DisplayAlerts = False
        Worksheets(SheetName).delete
Application.DisplayAlerts = True
    ElseIf SheetName = "" Then Exit Sub
    ElseIf Sheet.Name <> SheetName Then MsgBox "Sheet Doesn't Exist"
    End If
Next Sheet
End Sub

Delete Sheet If Exists Using VBA in Excel

Case 1:
Input = Sheet4, which exists in the workbook.

Delete Sheet If Exists Using VBA in Excel

It shows the alert to confirm the deletion. Click the Delete button.

Delete Sheet If Exists Using VBA in Excel

The Sheet4 is deleted from the workbook.

Case 2:
Input = Sheet4, could be anything that doesn’t exist in the workbook.

It shows the message that the worksheet doesn’t exist.

Delete Sheet If Exists Using VBA in Excel

Case 3:
Input = left black and press Enter or hit OK to exit.

Read More: [Fixed!] Delete Sheet Not Working in Excel (2 Solutions)


Notes

  • The Delete method by default shows the confirmation dialogue box message to confirm whether the user is sure to delete the worksheet or not. When the method works on the Worksheet object, it returns a Boolean true or false. If the user chooses the Cancel button the return value is False. On the other hand, it returns True if the user chooses the Delete button.

Conclusion

Now, we know how to delete a sheet if exists in the workbook with 4 different examples. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo