How to Clear Contents of a Sheet with Excel VBA (5 Examples)

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.


Download Practice Workbook

You can download the workbook used for the demonstration of the article from the download link below.


5 Suitable Examples to Clear Contents of a Sheet Using Excel VBA

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 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.

dataset for excel vba clear contents of sheet

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, click here to know how to show 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

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 Developers tab on your ribbon.
  • Then select Visual Basic from the Code group of the tab.

opening vba window for excel vba clear contents of sheet

  • 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.

inserting module in the vba window

  • 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.

excel vba clear contents of sheet

Read More: Excel VBA to Clear Contents of Range (3 Suitable Cases)


2. Clearing 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 Developers tab on your ribbon.
  • Then select Visual Basic from the Code group of the tab.

opening vba window

  • 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.

inserting module for excel vba clear contents of sheet

  • Now select the module and insert the following code in it.
Sub Clear_Specific_Sheet()
Sheets("Sheet3").Cells.ClearContents
End Sub

Replace Sheet3 with the name of the sheet you are trying to clear.

  • Finally, press F5 to run the code.

excel vba clear contents of specific sheet

Using this VBA will clear the contents of a specific sheet in Excel.

Read More: How to Clear Contents in Excel Without Deleting Formatting


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 Developers 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 Integer

This portion declares i as a variable that can be integers only.

For i = 4 To 6

This line starts the For loop. It ranges from 4 to 6.

Worksheets(i).Cells.ClearContents

The 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 i

This 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.

excel vba clear contents of multiple sheet- first sheet

This is the 5th one.

excel vba clear contents of multiple sheets - second sheet

And this is the 6th sheet of the workbook.

Read More: How to Clear Multiple Cells in Excel (2 Effective Methods)


Similar Readings


4. Clearing Contents of Whole Workbook

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 Developers 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 Worksheet

This portion declares Wsht as a variable that can be used instead of repeating worksheets every time.

For Each Wsht In ActiveWorkbook.Worksheets

This line starts the For loop. It iterates for every sheet in the workbook.

Wsht.Cells.ClearContents

This line clears the cell contents of a single worksheet. In every For loop, it is the sheet that comes within the iteration.

Next Wsht

This 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.

excel vba clear contents of sheet

This is how you can clear all the contents from every sheet in an Excel workbook using VBA.

Read More: How to Clear Cell Contents Based on Condition in Excel (7 Ways)


5. Clearing Contents of a Sheet from a Closed Workbook

This is an interesting one. As 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 in.

location of the closed file

And this is the sheet we are going to clear using the VBA code.

sheet of the closed file to excel vba clear contents of sheet

Steps:

  • First, go to the Developers 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 Workbook

This line declares wbk as a variable to mention a workbook

Application.ScreenUpdating = False

It 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").Activate

It 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.ClearContents

This line will clear all the contents of all the cells in the previously selected sheet.

wbk.Close savechanges:=True

The line saves the workbook

Application.ScreenUpdating = False

At 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.

excel vba cleared contents of sheet

Read More: Excel VBA: Clear Contents If Cell Contains Specific Values


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.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo