In this article, we will learn to clear contents in Excel without deleting formulas using VBA. Sometimes, we need to clear the contents of the sheet but keep the formulas for others. This helps other users to get the desired results easily. Today, we will demonstrate 4 ideal examples. Using these examples, you can easily clear contents in Excel using VBA. So, without any delay, let’s start the discussion.
How to Clear Contents in Excel Without Deleting Formulas Using VBA: 4 Ideal Examples
To explain the examples, we will use a dataset that contains information about the Sales Amount of the first 3 months of some sellers. Here, we have calculated the month-wise, person-wise, and total sales amount using the SUM function. You can see that ranges F5:F8, C9:E9, and cell D11 contain formulas. In the following examples, we will clear the contents without deleting the formulas.
1. Clear Contents from Range Without Deleting Formulas Using VBA in Excel
In the first example, we will clear contents from a range without deleting formulas using VBA in Excel. Here, we will have to type the desired range inside the code. The steps of this example are simple and we will use them in all 4 examples. So, we need to pay attention to the steps. Let’s follow the steps below to learn the example.
STEPS:
- First of all, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
- Alternatively, you can press the Alt + F11 keys together to open the Visual Basic window.
- Secondly, click on the View tab of the Visual Basic A drop-down will appear.
- Select Immediate Window from there.
- Also, you can press the Alt + G keys to open the Immediate window.
- Thirdly, place the cursor in the Immediate Window.
- After that, type the code below in the Immediate Window:
Sheet2.Range("B5:F11").SpecialCells(xlCellTypeConstants).ClearContents- Now, place the cursor at the end of the code and press Enter.
In this code, you need to change the sheet number and range according to your needs. For example, you want to clear contents from the range D5:G10 in Sheet 5. Then, you need to type the below code:
Sheet5.Range("D5:G10").SpecialCells(xlCellTypeConstants).ClearContents- As a result, you will be able to clear the contents without deleting the formulas.
Read More: Excel VBA to Clear Contents of Range
2. Insert VBA to Remove Contents from Selection Without Erasing Formulas
In the second example, we will clear contents from a selection of cells without deleting the formulas using VBA. The steps of this example this similar to the previous one. But there is a small change inside the code. Also, you need to select the cells from where you want to clear contents first. Let’s observe the steps below to see how we can apply the example.
STEPS:
- Firstly, select the cells from where you want to clear the contents. Here, we have selected cells C5 to F9.
- In the second step, press Alt + F11 to open the Visual Basic window.
- After that, press Alt + G to open the Immediate Window.
- In the Immediate Window, type the code below:
Selection.SpecialCells(xlCellTypeConstants, 23).Clear- Place the cursor at the end of the code.
- Finally, hit Enter to see results like the picture below.
3. Apply Excel VBA for Contents Omitting from Entire Sheet Without Deleting Formulas
We have clear content from a range and selection in the first two examples. Now, we will show the process to clear contents from the entire sheet without deleting formulas. In the dataset below, the Total column and row contain formulas.
Let’s observe the steps below to learn more about it.
STEPS:
- In the first place, navigate to the Developer tab and select Visual Basic. It will open the Visual Basic window.
- Alternatively, you can press the Alt + F11 keys together to open the Visual Basic window.
- After that, press Alt + G to open the Immediate Window.
- In the Immediate Window, type the code below:
Activesheet.cells.SpecialCells(xlCellTypeConstants, 23).Clear- Place the cursor at the end of the code.
- Lastly, hit Enter to see results like the picture below.
Read More: How to Clear Contents of a Sheet with Excel VBA
4. Clear Contents from Workbook Without Removing Formulas Using Excel VBA
In the last example, we will show the code to clear contents from the whole workbook without removing formulas. Let’s follow the steps below to see how we clear contents from the whole workbook.
STEPS:
- Firstly, press Alt + F11 to open the Visual Basic window.
- Secondly, hit Alt + G to open the Immediate Window.
- After that, type the code in the Immediate Window:
For Each wks In Worksheets: wks.Cells.SpecialCells(xlCellTypeConstants, 23).Clear: Next wks- Place the cursor at the end of the code.
- Finally, press Enter to clear contents from the whole workbook.
Download Practice Workbook
You can download the practice book from here.
Conclusion
In this article, we have demonstrated 4 ideal examples of using VBA in Excel to Clear Contents Without Deleting Formulas. I hope this article will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
Related Articles
- Excel VBA to Clear Contents of Named Range
- How to Clear Cells in Excel with Button
- Excel VBA to Delete and Shift Left Any Cell or Column
- Excel VBA: Clear Contents If Cell Contains Specific Values
