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”.
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
- 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
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 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.
As I said, it deletes the active sheet, and you can also activate a sheet before you remove 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 starting of this tutorial.
Check if 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
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
More Tutorials on VBA Worksheets
- How to Create a New Sheet using VBA
- How to Activate and Select a Sheet using VBA
- How to Copy and Move a Sheet using VBA
- How to Rename a Sheet using VBA
- How to Hide and Unhide a Sheet using VBA
- How to Count Sheets using VBA
- How to Protect a Sheet using VBA
- How to Clear an Entire Sheet using VBA
- How to Check is a Sheet Exists using VBA
- How to Loop Through Sheets in a Workbook using VBA