One of the advantages of using spreadsheet software like Microsoft Excel instead of a handwritten one is the reusability. Sometimes you need to clear cell values for a whole set of new values in an already formatted dataset or worksheet. Of course, you can copy formats or delete cell contents after copying them individually. But this can be a long process in case there are too many formats. Also, there is a chance of too many spreadsheets being edited in a single workbook. In these cases, using VBA to clear contents comes in handy. In this tutorial, we will show you how to use VBA to clear the contents of a sheet in Excel.
The Visual Basic for Applications (VBA) is an event-driven programming language from Microsoft. It can be used to perform various tasks, sometimes otherwise impossible, in Microsoft Office applications. It comes in handy for complex applications like clearing contents without deleting the formatting of a sheet in Excel where you have to go through repetitive processes otherwise.
In this tutorial, we will be going over five different scenarios of clearing the contents of a cell. In all cases, we will be using the following dataset.
But don’t worry if you have a different dataset or a worksheet that looks different. These codes will work regardless of the type and format of the worksheet you have.
But you need to show the Developer tab on your ribbon first. If you don’t have one, you need to use Excel Options to display the developer tab on your ribbon. Once you have that, you can follow the one you need from the list below.
1. Clearing Contents of Active Sheet with Excel VBA
In this first tutorial, we will be going over clearing the contents of an active sheet only. This method will come in handy if you want to clear one sheet at a time or a list of individual ones that aren’t placed side by side. Follow these steps for a detailed guide.
Steps:
- First, go to the Developer tab on your ribbon.
- Then select Visual Basic from the Code group of the tab.
- As a result, the VBA window will open up.
- Now select the Insert tab in it.
- After that, select Module from the drop-down list.
- Now select the module and insert the following code in it.
Sub Clear_Active_Sheet()
ActiveSheet.Cells.ClearContents
End Sub- Finally, press F5 to instantly run the code.
The spreadsheet will now look like this.
2. Using Excel VBA to Remove Contents of a Specific Sheet
In this section, we will discuss how to clear the contents of a specific sheet, even if the sheet isn’t actively selected in the spreadsheet using VBA in Excel. For that, we will need to use a slightly different version of the code.
Follow these steps for more details.
Steps:
- First, go to the Developer tab on your ribbon.
- Then select Visual Basic from the Code group of the tab.
- As a result, the VBA window will open up.
- Now select the Insert tab in it.
- After that, select Module from the drop-down list.
- Now 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 are trying to clear.
- Finally, press F5 to run the code.
Using this VBA will clear the contents of a specific sheet in Excel.
Read More: Excel VBA: Clear Contents If Cell Contains Specific Values
3. Clearing Contents of Multiple Sheets
Now we will demonstrate the code to clear the contents of multiple sheets in Excel using VBA. Keep in mind that, the sheets need to be side by side with each other to use this code for the purpose. As the code uses a for loop for iteration, it will remove contents of every single sheet’s content in between.
Steps:
- First, go to the Developer tab on your ribbon.
- Then select Visual Basic from the Code group of the tab.
- As a result, the VBA window will open up.
- Now select the Insert tab in it.
- After that, select Module from the drop-down list.
- Now 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 Sub🔎 Explanation of the Code
Dim i As IntegerThis portion declares i as a variable that can be integers only.
For i = 4 To 6This line starts the For loop. It ranges from 4 to 6.
Worksheets(i).Cells.ClearContentsThe code takes 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 iThis line restarts the For loop as long as the value is within the declaration.
- Finally, press F5 to run the code.
This will clear the contents of the 4th to 6th sheet of the workbook.
This is the 4th sheet.
This is the 5th one.
And this is the 6th sheet of the workbook.
4. Deleting Contents of the Whole Workbook with Excel VBA
In this section, we will discuss the code to clear all contents of every sheet in a single workbook. This code is relatively straightforward as the task is too.
Follow these steps to see the details of the code and how to use it.
Steps:
- First, go to the Developer tab on your ribbon.
- Then select Visual Basic from the Code group of the tab.
- As a result, the VBA window will open up.
- Now select the Insert tab in it.
- After that, select Module from the drop-down list.
- Now 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 Sub🔎 Explanation of the Code
Dim Wsht As WorksheetThis portion declares Wsht as a variable that can be used instead of repeating worksheets every time.
For Each Wsht In ActiveWorkbook.WorksheetsThis line starts the For loop. It iterates for every sheet in the workbook.
Wsht.Cells.ClearContentsThis line clears the cell contents of a single worksheet. In every For loop, it is the sheet that comes within the iteration.
Next WshtThis line restarts the For loop as long as the value is within the declaration.
- Finally, press F5 to run the code.
All of the sheets in the workbook will be like this after this.
This is how you can clear all the contents from every sheet in an Excel workbook using VBA.
5. Applying VBA to Clear Contents of a Sheet from a Closed Workbook
This is an interesting one. Generally, we are inclined to use VBA codes for the current workbook that we are using. Excel has ways to perform operations in a closed workbook saved in the computer’s memory without even opening them using VBA. In this section, we will focus on how to clear the contents of a sheet from a closed workbook in Excel using VBA. But you need to at least keep Microsoft Excel open for the purpose.
For this demonstration, we are using the same workbook. We are running the code from a different file. You can run the code by opening the same file too.
This is the path where our closed workbook resides.
And this is the sheet we are going to clear using the VBA code.
Steps:
- First, go to the Developer tab on your ribbon.
- Then select Visual Basic from the Code group of the tab.
- As a result, the VBA window will open up.
- Now select the Insert tab in it.
- After that, select Module from the drop-down list.
- Now 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 Sub🔎 Explanation of the Code
Dim wbk As WorkbookThis line declares wbk as a variable to mention a workbook
Application.ScreenUpdating = FalseIt prevents the real-time screen time from updating.
Set wbk = Workbooks.Open("C:\Users\user\Documents\Files\Clear Contents of Sheet.xlsm")The target workbook is set through this step. You should change what is inside the quotation mark to the location of your target workbook.
wbk.Sheets("Sheet8").ActivateIt will make the first sheet of our workbook active. Again, you should replace Sheet8 with the sheet’s name in the workbook you want to clear.
Cells.ClearContentsThis line will clear all the contents of all the cells in the previously selected sheet.
wbk.Close savechanges:=TrueThe line saves the workbook
Application.ScreenUpdating = FalseAt this point, the real-time screen update is turned on again.
- Finally, press F5 to run the code.
The sheets in the workbook will be like this after this.
Download Practice Workbook
You can download the workbook used for the demonstration of the article from the download link below.
Conclusion
These were different scenarios and codes for these different scenarios to clear the contents of sheets in Excel using VBA. Hopefully, you have understood the basics of the codes and use them to your needs accordingly. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
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
