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:

vba delete sheet

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

 

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)