VBA Delete or Clear Worksheet
In this Article
This tutorial will teach you how to delete or clear a worksheet using VBA.
Use the delete command to delete a worksheet.
Delete Worksheet by Name
Delete Worksheet by Index Number
This code deletes the first worksheet in the workbook:
This code deletes the last worksheet in the workbook:
Delete Worksheet Without Prompt
When you attempt to delete a worksheet, Excel will ask you to confirm your action:
You can disable these prompts (alerts) by toggling DisplayAlerts:
Application.DisplayAlerts = False Sheets("Sheet1").Delete Application.DisplayAlerts = True
Delete Sheet If It Exists
If you attempt to delete a worksheet that does not exist, VBA will generate an error. With On Error Resume Next you can tell VBA to delete a sheet if it exists, otherwise skip to the next line of code:
On Error Resume Next Sheets("Sheet1").Delete On Error GoTo 0
You could also use our RangeExists function to check if a sheet exists and if so delete it.:
If RangeExists("Sheet1") Then Sheets("Sheet1").Delete End If
This code will clear an entire sheet of contents, formats, and everything else:
Clear Sheet Contents
This code will clear an entire sheet’s contents. It will leave formatting, comments, and everything else alone:
Clear Sheet UsedRange
The above examples will clear ALL cells in a worksheet. This can be very time consuming in large sheets. If you use the UsedRange instead, VBA will only clear the “used” cells that contain values, formats, etc.