Home / VBA / How to CLEAR an Entire Sheet using VBA in Excel

How to CLEAR an Entire Sheet using VBA in Excel

- Written by Puneet

Contents hide
Clear the Active Sheet
Clear a Specific Sheet
Clear All the Sheets from a Workbook
Clear a Sheet from a Specific Workbook
Clear a Sheet from a Workbook that is Closed
Clear a Sheet with a Specific Word in the Name
Other Things to Clear
Important Notes
Related Tutorials

Let’s say you have a monthly report in Excel that needs to be updated with new data each month. Instead of manually deleting the old data, you can use a VBA code to clear the entire sheet quickly. This way, you can start fresh each month quickly.

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. Clearing a sheet with a VBA code is like resetting everything in the worksheet. This tutorial will look at using the CLEAR method in VBA.

Clear the Active Sheet

If you want to clear all the content from the activate sheet, the code would be like the one 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, we have used CELLS to refer to the entire worksheet and CLEAR to clear it.

When you use this method, it clear everything from all the cells from values to formatting.

Clear a Specific Sheet

There’s one thing you need to note down: 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:

Worksheets("Sheet1").Activate
Cells.Clear

In this code, first we have used the Worksheet object to refer to the “Sheet1”. And then, use the activate method. And in the end, Cells.Clear to the clear the entire sheet.

Clear All the Sheets from a Workbook

Sub ClearAllSheets()
    Dim ws As Worksheet
    Dim resultMessage As String
    resultMessage = "Cleared Sheets:" & vbCrLf & vbCrLf ' Start the message string
    For Each ws In ThisWorkbook.Sheets
        ws.Cells.Clear
        resultMessage = resultMessage & ws.Name & vbCrLf ' Append each sheet name to the message string
    Next ws
    MsgBox resultMessage, vbInformation ' Display all cleared sheet names at once
End Sub

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

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 when running this code.

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

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

Clear a Sheet from a Workbook that is Closed

And in the below code, we have referred to “Sheet1” from the workbook “sample-file”, stored in 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

In this code, we have used a variable Dim wb As Workbook to specify a workbook saved on your system. And then we have to disable screen updating with Application.ScreenUpdating = False.

We have used this Workbook.Open method to open the workbook from the system. You need to specify the entire location path in the code Set wb = Workbooks.Open("C:\Users\Dell\Desktop\sample-file.xlsx").

As we did earlier, we need to write a line of code wb.Sheets("Sheet1").Activate to activate the worksheet in the workbook you have specified.

Then Cells.Clear , to clear the code. Once the sheet is clear, you need to close the workbook, and for this, you need to use the code wb.Close SaveChanges:=True.

In the end, enable the screen updating with the line of the code Application.ScreenUpdating = False.

Clear a Sheet with a Specific Word in the Name

If you have multiple sheets in the a workbook containing a specific word in the name, well, in this case, you can use the loop to loop through all the sheets and clear the sheet with that word in the name.

Sub clear_hello_sheet()
    Dim iSheet As Worksheet
    For Each iSheet In ThisWorkbook.Worksheets
        If InStr(1, iSheet.Name, "Hello", vbTextCompare) > 0 Then
            iSheet.Cells.Clear
        End If
    Next iSheet
End Sub

In this code, we have a a For Each look which loops to all the worksheets in the workbook. Then, the InStr function, checks name of the each worksheet for the work “Hello”. And if the word is there in the name will clears that worksheet.

Other Things to Clear

As we have learned, .Clear method clears everything from the cells in the worksheet. But if you want to clear a specific part from the worksheet, there are different methods in VBA for this.

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

Important Notes

When you are clearing a worksheet in Excel using a VBA‌ code, there are a few things that you need to take care quite a few things:

  • When you clear a sheet with a code, it’s irreversible, and you can’t undo it. So, make sure to have a backup of the data from the sheet.
  • Make sure to check there are formulas in the worksheet which are dependent on the data from the sheet which you want to clear. This can break all those formulas.
  • While referring to a sheet, make sure to check if the name of the sheet is correct. Referring to a wrong sheet by mistake can create a problem.
  • While it won’t take much time to clear a sheet, but, still it’s better to disable the screen updating to increae the speed of the code.

Related Tutorials