VBA Delete or Clear Worksheet

Associated Files Download Links

This tutorial will teach you how to delete or clear a worksheet using VBA.

Delete Worksheet

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:

vba delete sheet

You can disable these prompts (alerts) by toggling DisplayAlerts:

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:

 

You could also use our RangeExists function to check if a sheet exists and if so delete it.:

 

Clear Sheet

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.