How to Clear Contents Without Deleting Formulas Using VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

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

how to clear contents in excel without deleting formulas using vba


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.

Clear Contents from Range Without Deleting Formulas Using VBA in Excel

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

Clear Contents from Range Without Deleting Formulas Using VBA in Excel

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 contents. Here, we have selected Cells C5 to F9.

Insert VBA to Remove Contents from Selection Without Erasing Formulas

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

Insert VBA to Remove Contents from Selection Without Erasing Formulas

  • Finally, hit Enter to see results like the picture below.

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


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.

Apply Excel VBA for Contents Omitting from Entire Sheet Without Deleting Formulas

Let’s observe the steps below to know 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.

Apply Excel VBA for Contents Omitting from Entire Sheet Without Deleting Formulas

  • Lastly, hit Enter to see results like the picture below.

Read More: How to Clear Cells in 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.

Clear Contents from Workbook Without Removing Formulas Using Excel VBA

  • Finally, press Enter to clear contents from the whole workbook.

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


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

Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo