Use Cells.Clear on the sheet you want to empty. It removes values, formulas and formatting from every cell:
ThisWorkbook.Worksheets("Sheet1").Cells.Clear
The sheet does not need to be active first. Use ActiveSheet.Cells.Clear only when you mean whichever sheet is on screen.
To keep the formatting and remove only values and formulas, use Cells.ClearContents. Cells.ClearFormats does the reverse, and ClearComments, ClearNotes and ClearHyperlinks remove only those.
To clear last month’s data but keep the header row, clear from row 2 down:
With ThisWorkbook.Worksheets("Report")
.Rows("2:" & .Rows.Count).ClearContents
End With
To clear every sheet in the file, loop For Each ws In ThisWorkbook.Worksheets and run ws.Cells.Clear inside it.
Two things catch people out. Clear leaves shapes, charts, pictures and buttons in place, so remove those with ws.DrawingObjects.Delete. And a macro clear cannot be undone with Ctrl + Z, so test on a copy first.
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.
To clear an entire sheet using a VBA code in one go, you need to use two things: first is the CLEAR method, and second is 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 active sheet, the code would be like the one below.
Cells.Clear
When you run this code, it will clear everything from all the cells of 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 clears everything from all the cells, from values to formatting. Just note that Clear works on cells only: pictures, shapes and charts that sit on the sheet stay there (see the code to remove them below).
Clear a Specific Sheet
To clear a specific sheet, you can activate it first and then clear its cells. So let’s say you want to clear “Sheet1”, the code would be like:
Worksheets("Sheet1").Activate
Cells.Clear
In this code, first we have used the Worksheet object to refer to “Sheet1”. And then, we used the Activate method. And in the end, Cells.Clear to clear the entire sheet.
But you don’t have to activate the sheet. When you put the sheet before Cells, VBA clears it even when another sheet is active, and the code runs faster:
Sub clear_specific_sheet()
Worksheets("Sheet1").Cells.Clear
End Sub
In the code below, ThisWorkbook.Sheets also includes chart sheets, which have no cells. So, if your workbook has a chart sheet, the code stops with a “Type mismatch” error on it. In that case, change ThisWorkbook.Sheets to ThisWorkbook.Worksheets.
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 “Sheet1” in the workbook and clears it. You can also skip the activation and use Workbooks(“Book1”).Sheets(“Sheet1”).Cells.Clear.
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 sheet. 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 screen updating again with the line of code Application.ScreenUpdating = True. The code above has False in its last line, which leaves screen updating off; change it to True. Here’s the corrected code, which also clears the sheet without activating it:
Sub vba_clear_sheet_closed()
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open("C:\Users\Dell\Desktop\sample-file.xlsx")
wb.Sheets("Sheet1").Cells.Clear
wb.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
SaveChanges:=True saves the cleared sheet when the workbook closes, so the change is permanent.
Clear a Sheet with a Specific Word in the Name
If you have multiple sheets in a workbook containing a specific word in the name, in this case, you can use a 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 For Each loop which goes through all the worksheets in the workbook. Then, the InStr function checks the name of each worksheet for the word “Hello”. And if the word is there in the name, it clears that worksheet. As InStr uses vbTextCompare, it doesn’t matter if the name has “hello” or “HELLO”.
Other Things to Clear
As we have learned, the 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 of:
- 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 if there are formulas in other worksheets which depend on the data from the sheet you want to clear. Clearing it changes the results of 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, it’s still better to disable screen updating to increase the speed of the code.
Clear a Sheet but Keep the Header Row
For a monthly report, you often want to remove the old data but keep the headings. The following code clears the values (not the formatting) from row 2 down to the end of the used range:
Sub clear_sheet_keep_header()
With Worksheets("Sheet1").UsedRange
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1).ClearContents
End If
End With
End Sub
This assumes the headers are in the first row of the used range. Use Clear instead of ClearContents if you want to remove the formatting from those rows as well.
Remove Shapes, Charts and Pictures from a Sheet
As Cells.Clear doesn’t touch objects that sit on top of the cells, use this code to delete all the shapes, charts and pictures from the active sheet as well:
Sub delete_all_shapes()
Dim i As Long
For i = ActiveSheet.Shapes.Count To 1 Step -1
ActiveSheet.Shapes(i).Delete
Next i
End Sub
Frequently Asked Questions
What is the difference between Clear and ClearContents?
Clear removes everything from the cells: values, formulas, formatting, comments and notes. ClearContents removes only the values and formulas and keeps the formatting, which is handy when you want to reuse a formatted template.
Why do I get an error when clearing a sheet?
The usual reasons are a protected sheet (unprotect it first), a sheet name that doesn’t match (Error 9), or a chart sheet in a loop that uses Sheets instead of Worksheets.
Can I undo clearing a sheet with VBA?
No. Changes made by a macro can’t be undone with Ctrl + Z. Save a copy of the workbook, or close it without saving, if you cleared the wrong sheet.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.