How to CLEAR an Entire Sheet using VBA in Excel

To clear an entire sheet using a VBA code in one go, you need to use two things, first is the CLEAR method, and then CELLS to refer to all the cells of a worksheet.

Make sure to have the developer tab ON to open the code editor for VBA.

Clear the Activesheet

Let’s say if you want to clear all the content from the activate sheet, the code would be like below.

Cells.Clear

When you run this code, it will clear everything from all the cells from the active sheet, as I have already mentioned that we have used clear to refer to the entire worksheet and clear to clear it.

Clear a Specific Sheet

There’s one thing that you need to note down that to clear a sheet that sheet needs to be activated. So let’s say you want to clear the “Sheet1”, the code would be like:

Sheets("Sheet1").Activate
Cells.Clear

In this code, the first activates the “Sheet1” and the second line clears everything.

Other Things to Clear

You can also use the below methods to clear different things.

Cells.ClearContents 'to clear contents
Cells.ClearComments 'to clear Comments
Cells.ClearFormats 'to clears formatting
Cells.ClearHyperlinks 'to clear hyperlinks
Cells.ClearNotes 'to clear notes
Cells.ClearOutline 'to clears outline

Clear a Sheet from a Specific Workbook

The below code can refer to the workbook “Book1” and clear the sheet “Sheet1”. But make sure to have the workbook open at the time run this code.

Workbooks("Book1").Sheets("Sheet1").Activate
Cells.Clear

This code first activates the “Sheet1” from the book and clear it.

Clear a Sheet from a Workbook which is Closed

And in the below code, we have referred to the “Sheet1” from the workbook “sample-file”, stored on a specific location.

Sub vba_clear_sheet()

Dim wb As Workbook

Application.ScreenUpdating = False

Set wb = Workbooks.Open("C:\Users\Dell\Desktop\sample-file.xlsx")
wb.Sheets("Sheet1").Activate
Cells.Clear
wb.Close SaveChanges:=True

Application.ScreenUpdating = False

End Sub

More Tutorials on Sheets