VBA & Macro Problems

Clear Excel Cell Contents While Keeping Formulas and Formatting

Maira MehtabMaira Mehtab Nov 18, 2026 870 views

Question details

Clear entered values from specific Excel cells while preserving formulas and formatting.

How to Clear Excel Cell Contents While Keeping Formulas and Formatting
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to clear constants in selected ranges without affecting formulas or formatting.
Observed behavior
Expanding blank cells to entire rows can affect cells outside the intended range, including cells containing formulas.
Before you start

Identify the worksheet and ranges whose entered values you want to clear. Check that the target cells are editable and that you want to remove all constants in those ranges, including text and numbers.

Solution 1Recommended

Use VBA to clear constants in the target range

Use SpecialCells to clear entered values while leaving formulas and formatting intact.

SpecialCells(xlCellTypeConstants) selects cells containing constants, not formulas. ClearContents removes their contents without clearing cell formatting. Avoid EntireRow unless you intend to affect the full worksheet rows.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Add the macro

In the VBA editor, select Insert > Module and paste this code: Sub ClearEnteredValues() Dim target As Range Set target = ActiveSheet.Range("A9:J9") Dim constants As Range On Error Resume Next Set constants = target.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If Not constants Is Nothing Then constants.ClearContents End Sub

3
Set the target range

Replace A9:J9 with the range you want to clear. To process other ranges, repeat the selection and clearing logic for each range, resetting constants to Nothing before each SpecialCells call.

4
Run the macro

Activate the intended worksheet, then select Developer > Macros, choose ClearEnteredValues, and select Run.

Use VBA to clear constants in the target range
No constants found: SpecialCells raises an error when no matching constants exist. The code handles that case and leaves the range unchanged. Macro actions generally cannot be undone with Excel's Undo command.
Clear values in WPS Spreadsheets

Clear entered values while keeping formulas in WPS Spreadsheets

Use Go To Special in WPS Spreadsheets to select constants in a range, then clear their contents without removing formulas or formatting.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
  2. 2. Select the range: On the worksheet, select the cells whose entered values you want to clear.
  3. 3. Find constants: Open Go To Special from the Find or Go To menu, select Constants, and confirm the selection.
  4. 4. Clear contents: Press Delete or use the Clear Contents command for the selected cells, then check that the formulas remain.
Select constants without selecting formula cellsKeep cell formatting when clearing contentsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Does ClearContents remove cell formatting?

No. In Excel VBA, ClearContents removes cell contents but leaves formatting intact. Use it instead of Clear when you want to preserve formatting.

Will formulas that return an empty string be cleared?

No. SpecialCells(xlCellTypeConstants) does not select formula cells, even if a formula displays an empty string.

Why does SpecialCells return an error?

SpecialCells raises an error if it finds no cells matching the requested type. Handle that case before calling ClearContents, as shown in the VBA example.

Can I clear only numbers and keep text constants?

Yes. In Excel, select the range and choose Home > Find & Select > Go To Special > Constants. Clear the other constant-type checkboxes, leave Numbers selected, and then clear the selected contents.