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.

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.

Method 1 – Utilizing the Clear Contents Option

Steps:

Go to the Formulas taband click the Show Formulas button.

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

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.

The formulas will see the values as zeroes.

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.

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

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

Here are the results.

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

Steps:

Navigate to the Developer tab and click on Visual Basic.

Go to the Insert tab and select 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

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.