How To Delete A SHEET Using A VBA Code (Macro) - Excel Champs

Skip to content Tutorials

• Excel Basics
• Keyboard Shortcuts
• Excel Tips
• Excel Functions
• Excel Formulas
• Advanced Excel
• Pivot Table
• VBA
• Power‌ Query

Home / VBA / How to Delete a Sheet using VBA in Excel

- Written by Puneet

Contents hide What is Delete Method in VBA? Delete a Sheet using its Name Delete a Sheet without Warning (Prompt) Name of the Sheet from a Cell Delete the Sheet using the Sheet Number Delete the ActiveSheet Delete All the Sheets Other than Active Sheet Delete All the Sheets After a Specific Sheet Check IF the Sheet Exists Before Deleting Delete All the Worksheets in Workbook Related Tutorials

To delete a sheet using VBA, you need to use the VBA Delete method. You need to specify the sheet that you want to delete and then use this method. Let’s say if you want to delete the “Sheet1”, then you need to mention sheet1 and then type a dot (.) and in the end, type “Delete”.

What is Delete Method in VBA?

DELETE METHOD in VBA is used to remove a worksheet or a sheet from a workbook permanently. When you use the “Delete” method, the action is irreversible, which means once a sheet is deleted, it cannot be recovered, even through VBA.

In this tutorial, we will see different ways that you can use to delete a sheet using a VBA code. Make sure to have the developer tab on the ribbon from here you can get into the visual basic editor.

Delete a Sheet using its Name

Each sheet has a name, and you can use write a code to delete a sheet using the name. So, let’s say you want to delete the worksheet “Data”, the code would be:

Sub vba_delete_sheet() Sheets("Data").Delete End Sub

Note – Create a backup of your workbook before using a macro that deletes sheets. This ensures that you can recover your data if something goes wrong.

Delete a Sheet without Warning (Prompt)

When you delete a worksheet, Excel shows a message to confirm if you want to remove it or wish to cancel. And when you use a VBA code, in that case, Excel will also do that.

To deal with this, you can turn OFF the screen updating to delete a sheet and then turn it ON.

Application.DisplayAlerts = False Sheets("Data").Delete Application.DisplayAlerts = True

Note – Double-check the sheet name in the code, especially if you specify them directly. A wrong sheet name can lead to deleting the wrong sheet; you can only recover it if you have a backup.

Name of the Sheet from a Cell

Now let’s say you want to use a cell value to use the name of the worksheet. In that case, you need to use the VBA range object to do that.

Delete the Sheet using the Sheet Number

That’s right. You can use the sheet’s number to delete it. Here’s the code.

Sub vba_delete_sheet() Sheets(1).Delete End Sub

Delete the ActiveSheet

To delete the active sheet, you can use the “ActiveSheet” object instead of using the sheet name to specify the sheet.

ActiveSheet.Delete

As I said, it deletes the active sheet, and you can activate it before removing it. But necessarily, you don’t need to do that as you can refer to a sheet and delete it as we have seen at the start of this tutorial.

Delete All the Sheets Other than Active Sheet

Sub delete_all_the_sheets_not_active() Dim ws As Worksheet ' Disable alerts to prevent confirmation messages for each sheet being deleted Application.DisplayAlerts = False ' Loop through each worksheet in the workbook For Each ws In ThisWorkbook.Worksheets If Not ws Is ActiveSheet Then ' Delete the sheet if it is not the active sheet ws.Delete End If Next ws ' Re-enable alerts after operation is complete Application.DisplayAlerts = True End Sub

Note – Before running this code, ensure that the workbook has more than one sheet, as Excel does not allow all sheets to be deleted.

Delete All the Sheets After a Specific Sheet

This code shows the user an input box to enter the name of a sheet and then deletes all the sheets that follow it in the workbook. It finds the specified sheet and then uses a starting point to start deleting all the sheets after that.

Sub delete_sheets_after_specified_sheet() Dim ws As Worksheet Dim startDeleting As Boolean Dim sheetName As String ' Get the name of the sheet after which all sheets will be deleted sheetName = InputBox("Enter the name of the sheet after which all other sheets will be deleted:") ' Initialize flag to false startDeleting = False ' Disable alerts to prevent pop-up confirmation messages Application.DisplayAlerts = False ' Loop through all worksheets in the workbook For Each ws In ThisWorkbook.Worksheets If startDeleting Then ' If flag is true, delete the sheet ws.Delete ElseIf ws.Name = sheetName Then ' Set flag to true if the current sheet is the one specified startDeleting = True End If Next ws ' Re-enable alerts after operation is complete Application.DisplayAlerts = True End Sub

Check IF the Sheet Exists Before Deleting

You can also write code in a way that it can check if the sheet exists or not and then deletes it.

Sub check_sheet_delete() Dim ws As Worksheet Dim mySheet As Variant mySheet = InputBox("enter sheet name") Application.DisplayAlerts = False For Each ws In ThisWorkbook.Worksheets If mySheet = ws.Name Then ws.Delete End If Next ws Application.DisplayAlerts = True End Sub

This code removes a specific worksheet from a workbook based on user input. It ask users to enter the worksheet name they wish to delete through an input box. Once the name is entered, the macro temporarily turns off alert messages to prevent any interruption during deleting the sheet.

Delete All the Worksheets in Workbook

I’m sure you have this question in your mind, but I’m afraid it’s impossible to delete all the worksheets that you have in the workbook. You must have at least one worksheet left. But I have found a solution to this problem. You can insert a new sheet that’s a blank one and then delete all which are already there.

Here’s the code: This code adds a new sheet and deletes all the other sheets.

Sub vba_delete_all_worksheets() Dim ws As Worksheet Dim mySheet As String mySheet = "BlankSheet-" & Format(Now, "SS") Sheets.Add.Name = mySheet Application.DisplayAlerts = False For Each ws In ThisWorkbook.Worksheets If ws.Name <> mySheet Then ws.Delete End If Next ws Application.DisplayAlerts = True End Sub What is VBA

Related Tutorials

• CLEAR an Entire Sheet using VBA in Excel
• Copy and Move a Sheet in Excel using VBA
• COUNT Sheets using VBA in Excel
• Hide & Unhide a Sheet using VBA in Excel
• PROTECT and UNPROTECT a Sheet using VBA in Excel
• RENAME a Sheet using VBA in Excel

• Write a VBA Code to Create a New Sheet in Excel
• VBA Worksheet Object
• Activate a Sheet using VBA
• VBA Check IF a Sheet Exists

Leave a Comment Cancel reply

Comment

Name Email Website

Δ

About Excel Champs

Excel Champs is one of the top Excel blogs where you can find some of the most amazing tutorials on Excel basics, formulas, VBA, charts, and much more...

Contact - Terms - Policy

Quick Links

• Free Online Excel Courses
• Excel Blog
• Excel Inventory Template
• Testimonials

Excel Guides

• How to use Excel
• Describe Your Excel Skills in Resume
• Excel for Accountants
• Excel Tips and Tricks
• What is VBA
• Pivot Tables in Excel
• Excel Skills