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.

 

You may also like some of this related content...

Ads