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