VBA Delete or Clear Worksheet

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

Sheets("Sheet1").Delete

Delete Worksheet by Index Number

This code deletes the first worksheet in the workbook:

Sheets(1).Delete

This code deletes the last worksheet in the workbook:

Sheets(Sheets.Count).Delete

 

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

 

Clear Sheet

This code will clear an entire sheet of contents, formats, and everything else:

Sheets("Sheet1").Cells.Clear

Clear Sheet Contents

This code will clear an entire sheet’s contents. It will leave formatting, comments, and everything else alone:

Sheets("Sheet1").Cells.ClearContents

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.

Sheets("Sheet1").UsedRange.Clear