Delete and Clear Worksheet in Excel VBA

In this post, let’s have a look at different options that you have to Delete and Clear worksheet in Excel VBA.

To run the VBA, first you will need to do the following.

• Under the Developer tab, click the Visual Basic option.
• On the insert option in the menu and click the module
• Enter the code and run it.

Delete Worksheet

To delete a worksheet in excel VBA, you can use the delete command. There are different ways in which can delete the worksheet in Excel.

Delete Worksheet by Name

To Delete Worksheet by Name

Code

Sheets("Sheet1").Delete

Delete Worksheet by Index Number

To Delete Worksheet by Index Number

Code

Sheets(1).Delete

Here, 1 represents the index number of the sheets. To delete the last worksheet in the workbook

Code

Sheets (Sheets.Count).Delete

Delete Worksheet Without Prompt

You can disable the prompts while deleting the worksheets. You can do this by toggling DisplayAlerts:

Application.DisplayAlerts = False

Sheets("Sheet1").Delete

Application.DisplayAlerts = True

You can see that the worksheet gets deleted without a prompt.

Clear Sheet in Excel VBA

To clear sheet in Excel VBA, you can use the clear command

Clear ActiveSheet

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

Code

Cells.Clear

Clear Contents

To clear only the cell contents

Code:

Cells.ClearContents

Clear Formats

To clear only the cell formats

Code:

Cells.ClearFormats

Clear Sheet (By Name)

To clear a specific worksheet

Code

Sheets("Sheet1").Cells.Clear