How to Clear Contents Without Deleting Formulas Using VBA in Excel: 4 Methods

Method 1 – Clear Contents from Range Without Deleting Formulas Using VBA in Excel

STEPS:

• Go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
• Press the Alt + F11 keys together to open the Visual Basic window.

• Cclick on the View tab of the Visual Basic, a drop-down will appear.
• Select Immediate Window from there.
• Press the Alt + G keys to open the Immediate window.

• Place the cursor in the Immediate Window.

• Type the code below in the Immediate Window:

Sheet2.Range("B5:F11").SpecialCells(xlCellTypeConstants).ClearContents

• Place the cursor at the end of the code and press Enter.

Sheet5.Range("D5:G10").SpecialCells(xlCellTypeConstants).ClearContents

• Clear the contents without deleting the formulas.

Method 2 – Insert VBA to Remove Contents from Selection Without Erasing Formulas

STEPS:

• Select the cells from where you want to clear the contents. We selected cells C5 to F9.

• Press Alt + F11 to open the Visual Basic window.
• 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.

• Hit Enter to see results like the picture below.

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

STEPS:

• Navigate to the Developer tab and select Visual Basic. It will open the Visual Basic window.
• Press the Alt + F11 keys together to open the Visual Basic window.
• 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.

• Hit Enter to see results like the picture below.

Method 4 – Clear Contents from Workbook Without Removing Formulas Using Excel VBA

STEPS:

• Press Alt + F11 to open the Visual Basic window.
• Hit Alt + G to open the Immediate Window.
• 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.

• Press Enter to clear contents from the whole workbook.

Download Practice Workbook

You can download the practice book from here.

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