If you are searching for a solution or some special tricks to clear contents of range with Excel VBA then you have landed in the right place. There are some easy steps to clear the contents of the range with Excel VBA. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.
Excel VBA to Clear Contents of Range: 3 Suitable Cases
Suppose, you have a dataset that you may want to reuse clearing existing cells. So, you can use VBA code to clear the contents of an Excel file. In this section, I will show you the quick and easy methods to clear the contents of range with Excel VBA on the Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used the Microsoft 365 version here. But you can use any other versions as of your availability. If anything in this article doesn’t work in your version then leave us a comment.
Case 1: Clear All Contents of Range
You can use both the Clear command and the Delete Command to clear the contents of a specific range in the current worksheet or other worksheets even from other workbooks also. I will show you all the cases one by one.
i. Clear Contents of Specific Range of Cells
If you want to clear the contents of a specific range then follow the steps below:
- For this, first, go to the top ribbon and press on the Developer, then press on the Visual Basic option from the menu.
- You can use ALT + F11 to open the “Microsoft Visual Basic for Applications” window if you don’t have the Developer tab added.
- Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.
- Now, a new “Module” window will appear. And Paste this VBA code into the box.
Using Clear Command:
- To run the code go to the top menu, press on the Run option, and here will open some other options and select the Run Sub/UserForm also you can simply press F5 to run the code.
- Using the Clear command, the cells will be cleared also the formatting will also be removed. But the empty cells will be there.
Using Delete Command:
You can also use the Delete command instead of the Clear command. Then paste the following code into the module.
Using the Delete command, when you run the code, the selected range of cells will be removed completely.
Difference Between Clear and Delete Command in Excel VBA:
The key difference between the Clear and Delete commands in Excel VBA is that the Delete command removes the selected range of cells completely and the Clear command removes only the cell value and the formatting but the empty cell will be there.
ii. Clear Contents of Full Worksheet
If you want to clear all cells of a worksheet then use the code given below. Here, I am clearing contents from the worksheet named “1.2”. You have to insert the name of the worksheet that you want to clear in the inverted commas.
Also, you can use the Delete command to remove all used cells in a worksheet. Then, you have to use the code given below. Just, change the name of the worksheet to inverted commas.
iii. Clear Contents of Active Worksheet
Sometimes, you may need to clear the contents of the active worksheet only. In this case, you have to a simple code given below:
You can also use the delete command to remove the cells completely from the active worksheet. for this, paste the following code into the module.
Case 2: Clear Contents of Range Keeping the Formatting
In the previous methods, you have noticed that you are removing the cell values along the cell formatting. So, if you want to clear contents without deleting formatting formatting, you can follow the below sections.
i. Contents of Specific Range
You can use the ClearContents command in Excel VBA to clear the contents of a specific range. For this, paste the following code into the module.
As a result, when you run the code you will see the selected cells are cleared but the formats are still remaining.
ii. Contents of Specific Worksheet
When you want to clear the contents of the specific worksheet while keeping the formats the same, you have to use the following code below:
🔎 VBA Code Explanation:
- Worksheets(“2.2”) calls the worksheet named “2.2”. If you want to call a worksheet of any other name then, insert the name of the worksheet in the inverted commas.
- And, Range(“B2:D4”) is specifying the range of cells that will be cleared. You can edit the cell range as to your need.
iii. Contents from Other Workbook
You can also clear the contents of another workbook using the VBA code. But, for this, you have to keep that workbook open. Use the following code below for this:
🔎 VBA Code Explanation:
- Workbooks(“file 1”) calls the workbook named “File 1”. While using your workbooks, you have to insert the name of the workbook in the inverted commas.
- Worksheets(“Sheet1”) calls the worksheet named “Sheet 1”. To call any other worksheet, just insert the name of the worksheet instead of “Sheet 1” in this code.
- And, Range(“B3:D13”) is specifying the range of cells that will be cleared. You can edit the cell range as your need.
Case 3: Clear Contents of Specific Range of All Worksheets at Once
If you need to clear the contents of a specific range of multiple worksheets you can do it at once using a VBA code. You have to use the For loop to do this task. Paste this code into the module to clear the contents of range B2:D4 of all worksheets. If you want to change the cell range then edit the 4th line and insert your data range.
Dim W_S As Worksheet
For Each W_S In ActiveWorkbook.Worksheets
Things to Remember
- Using the Clear command will only clear the cell values and formats.
- Delete command will remove the cells completely.
- ClearContents command will clear the values of the cells only and keep the cell formats untouched.
Download Practice Workbook
You can download the practice workbook from here:
In this article, you have found how to clear the contents of the range with Excel VBA. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.