In this tutorial, we will discuss five different scenarios involving clearing the contents of worksheets in Excel using VBA code. In all cases, we will be using the following dataset to illustrate our methods:
In order to work with VBA in general and the methods below in particular, the Developer tab must appear on your ribbon. If you don’t see it, use Excel Options to display the developer tab on your ribbon.
Example 1 – Clearing the Contents of the Active Sheet with Excel VBA
First, we’ll clear the contents of the active sheet only. This method is handy for clearing one sheet at a time.
Steps:
- Go to the Developer tab.
- Select Visual Basic from the Code group.
As a result, the VBA window will open.
- Select the Insert tab.
- Select Module from the drop-down list.
- Select the module and insert the following code in it:
Sub Clear_Active_Sheet()
ActiveSheet.Cells.ClearContents
End Sub- Press F5 to run the code.
The contents of the current sheet are cleared.
Example 2 – Removing the Contents of a Specific Sheet
Now let’s clear the contents of a specific sheet, even if that sheet isn’t actively selected. We’ll need a slightly modified version of the code used in Example 1.
Steps:
- Go to the Developer tab and select Visual Basic from the Code group.
As a result, the VBA window will open.
- Select the Insert tab.
- Select Module from the drop-down list.
- Select the module and insert the following code in it:
Sub Clear_Specific_Sheet()
Sheets("Sheet3").Cells.ClearContents
End SubReplace Sheet3 with the name of the sheet you want to clear.
- Press F5 to run the code.
The contents of the specified sheet are cleared.
Read More: Excel VBA: Clear Contents If Cell Contains Specific Values
Example 3 – Clearing the Contents of Multiple Adjacent Sheets
Now we will demonstrate the code to clear the contents of multiple adjacent sheets. Using a For loop for iteration, the code will remove the contents of every sheet between the specified start and end sheets.
Steps:
- Go to the Developer tab and select Visual Basic from the Code group.
As a result, the VBA window will open.
- Select the Insert tab.
- Select Module from the drop-down list.
- Select the module and insert the following code in it:
Sub Clear_Multiple_Sheets()
Dim i As Integer
For i = 4 To 6
Worksheets(i).Cells.ClearContents
Next i
End SubExplanation of the Code
Dim i As IntegerDeclares i as a variable of type Integer.
For i = 4 To 6Starts the For loop, which ranges from 4 to 6.
Worksheets(i).Cells.ClearContentsTakes the i-th worksheet and clears the contents of all cells. For example, it clears the contents of the 4th worksheet in the first iteration.
Next iIncrements the For loop and repeats the cell clearing operation while i <= 6.
- Press F5 to run the code.
The contents of the 4th to 6th sheets of the workbook are cleared.
This is the 4th sheet:
This is the 5th sheet:
And this is the 6th sheet:
Example 4 – Deleting the Contents of the Whole Workbook
Now let’s clear the contents of every sheet in a single workbook.
Steps:
- Go to the Developer tab and select Visual Basic from the Code group.
As a result, the VBA window will open up.
- Select the Insert tab.
- Select Module from the drop-down list.
- Select the module and insert the following code in it:
Sub Clear_All_sheets()
Dim Wsht As Worksheet
For Each Wsht In ActiveWorkbook.Worksheets
Wsht.Cells.ClearContents
Next Wsht
End SubExplanation of the Code
Dim Wsht As WorksheetDeclares Wsht as a variable that can be used instead of repeating worksheets every iteration.
For Each Wsht In ActiveWorkbook.WorksheetsStarts the For loop, which iterates through every sheet in the workbook.
Wsht.Cells.ClearContentsClears the cell contents of the single worksheet indicated by the For loop.
Next WshtIncrements and re-iterates the For loop.
- Press F5 to run the code.
All of the sheets in the workbook will look like this:
Example 5 – Clearing the Contents of a Sheet from a Closed Workbook
In our final scenario, we will clear the contents of a sheet from a closed workbook using VBA. Microsoft Excel will have to remain open for the purpose, but not the workbook in which the contents of a specified sheet will be cleared by the code.
We’ll use the same workbook as above, but run the code from a different file.
This is where our closed workbook is located:
And this is the sheet we are going to clear using the VBA code:
Steps:
- Go to the Developer tab and select Visual Basic from the Code group.
As a result, the VBA window will open up.
- Select the Insert tab.
- Select Module from the drop-down list.
- Select the module and insert the following code in it:
Sub Clear_Closed_Workbook()
Dim wbk As Workbook
Application.ScreenUpdating = False
Set wbk = Workbooks.Open("C:\Users\user\Documents\Files\Clear Contents of Sheet.xlsm")
wbk.Sheets("Sheet8").Activate
Cells.ClearContents
wbk.Close savechanges:=True
Application.ScreenUpdating = False
End SubExplanation of the Code
Dim wbk As WorkbookDeclares wbk as a variable to store a workbook.
Application.ScreenUpdating = FalsePrevents the real-time screen from updating while the code is running.
Set wbk = Workbooks.Open("C:\Users\user\Documents\Files\Clear Contents of Sheet.xlsm")Sets the target workbook. Change the path in the quotation mark to the location of your target workbook.
wbk.Sheets("Sheet8").ActivateMakes the first sheet of our workbook active. Replace Sheet8 with the appropriate sheet name you want to clear.
Cells.ClearContentsClears the contents of all the cells in the selected sheet.
wbk.Close savechanges:=TrueSaves the workbook.
Application.ScreenUpdating = FalseRe-enables the real-time screen update.
- Press F5 to run the code.
After opening the destination workbook, the specified sheet to be cleared will look like this:
Download Practice Workbook
Related Articles
- How to Clear Contents Without Deleting Formulas Using VBA in Excel
- Excel VBA to Clear Contents of Range
- Excel VBA to Clear Contents of Named Range
- Excel VBA to Delete and Shift Left Any Cell or Column
- How to Clear Cells in Excel with Button
