How to Clear Contents in Excel Without Deleting Formulas

The GIF below is an overview of clearing Excel values without deleting formulas. Here, a VBA code clears the contents without deleting formulas. When we run the code, it removes the contents without erasing the formulas.

Applying VBA to Clear Contents without Deleting Formulas


How to Clear Contents in Excel Without Deleting Formulas: 3 Ways

We’ll use the following dataset in the range B4:E10, with values for Unit Productions and Per Unit Cost in columns C and D. We want to clear the contents of the table without deleting formulas.

Dataset showing unit production and per unit cost of widget production


Method 1 – Utilizing the Clear Contents Option

Steps:

  • Go to the Formulas taband click the Show Formulas button.

Pressing Show formulas button in the formulas tab

  • This displays all the cells containing formulas and the cells containing only values. (B5:D10 cells)

Displaying cells with and without formulas

  • Select the B5:D10 cells and go to the Home tab.
  • Click the Clear drop-down and press Clear Contents to remove values without affecting formulas. You can also press the Delete key to erase the contents of the cells.

Using Clear contents in the home ribbon

  • The formulas will see the values as zeroes.

Cleared contents with clear contents option without deleting formulas


Method 2 – Clearing Contents Without Deleting Formulas Using the Go To Special Command

Steps:

  • Click on the Find & Select option and choose Go To Special.

Applying Go To Special in the Find & Select tab

  • The Go To Special window appears.
  • Check the Constants radio button, uncheck the Text option, and hit OK.

Enabling constants option and unchecking text option

  • This will select the B5:D10 cells.
  • Press the Delete key.

Selecting cells and pressing delete key

  • Here are the results.

cleared contents using Go To special without affecting formulas

Read More: How to Clear Multiple Cells in Excel


Method 3 – Applying VBA Code to Erase Contents Without Deleting Formulas

Steps:

  • Navigate to the Developer tab and click on Visual Basic.

Launching visual basic in the developer tab

  • Go to the Insert tab and select Module.

Inserting module

  • Copy the code from here and paste it into the window.
Sub clear_content_keep_formula()

    Dim val As Range

    Set val = Sheet6.Range("C5:D10").SpecialCells(xlCellTypeConstants)
    On Error Resume Next
    val.ClearContents

End Sub

VBA for clearing contents without deleting formulas

Code Breakdown
  • We named the sub-routine clear_content_keep_formula()
  • We defined the variable val as Range.
  • The Set statement stores the worksheet number (Sheet 6) and the range of cells (C5:D10).
  • The ClearContents method clears cells containing constant values.

Note: Change the sheet number and enter the appropriate cell range when using this VBA code.

VBA code explanation

  • Click the Run button or press the F5 key to execute the macro.

Running VBA macro

  • The results should look like the screenshot given below.

Using VBA macro to clear contents in excel without deleting formulas


How to Clear All Content in Excel Without Deleting Formatting

Steps:

  • Hold down the CTRL key and select the B5:E10 and C12:E12 ranges.
  • Press the Delete key.

Selecting multiple ranges with ctrl key and pressing delete

  • Here’s the output.

cleared contents without affecting formatting

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


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice.

Practice Section for clear contents in excel without deleting formulas


Download the Practice Workbook


Related Articles


<< Go Back to Clear Contents in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

2 Comments
  1. Reply
    Mongwongprue Marma Nov 10, 2023 at 11:55 PM

    Excellent in a word. However, Thanks a lot.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo