VBA code to delete data in a sheet

Return to VBA Code Examples

In this Article

• Delete Worksheet
  • Delete Worksheet Without Prompt
  • Delete Sheet If It Exists
• Clear Sheet
  • Clear Sheet Contents
  • Clear Sheet UsedRange

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More!!

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

Return to VBA Code Examples

In this Article

• Clear ActiveSheet
  • Clear Everything (Contents, Formats, Comments, etc.)
  • Clear Contents
  • Clear Formats
  • Delete Worksheet UsedRange
• Clear Sheet (By Name)
• Clear Worksheet (From Variable)

In VBA it’s fast and easy to clear an entire sheet (or worksheet).

Clear ActiveSheet

Clear Everything (Contents, Formats, Comments, etc.)

This will clear the Activesheet’s cells of all cell properties: contents, formats, comments, etc:

Cells.Clear

Clear Contents

Instead, you can clear ONLY the cell contents:

Cells.ClearContents

Clear Formats

or only the Cell Formats:

Cells.ClearFormats

By typing: Cells.Clear into the VBA Editor you can see the list of Clear methods available to you:

Delete Worksheet UsedRange

You can also delete the entire worksheet’s UsedRange. This can also delete objects (shapes, charts, textboxes).

ActiveSheet.UsedRange.Delete

Clear Sheet (By Name)

To clear a specific sheet use the following code where “Sheet1” is the name of the sheet to clear:

Sheets("Sheet1").Cells.Clear

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More!!

Clear Worksheet (From Variable)

To clear a sheet defined by an object variable use the following code:

dim ws as worksheet

Set ws = Sheets("Sheet1")

ws.Cells.Clear