VBA & Macro Problems

How to Clear Excel Cells Without Deleting Formulas Using VBA

Kushani Nimanthika Oct 8, 2026 869 views

Question details

The user needs a macro to clear data in specific columns (B through E) when a condition is met in another column (F), while preserving the formulas in column F.

How to Clear Excel Cells Without Deleting Formulas Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating data cleanup using a VBA macro based on specific conditional outputs without destroying the underlying formula logic in adjacent cells.
Observed behavior: Clearing the entire row to remove data successfully deletes the target cells but inadvertently deletes the formulas located in F44:F112.

Before you start

Before running any VBA macros to delete or clear data, ensure you save a copy of your workbook. Actions executed by macros cannot be undone using the standard Undo (Ctrl+Z) shortcut.

Solution 1Recommended

Use VBA to Clear Specific Cell Ranges (B:E) Instead of the Entire Row

Modify your macro to explicitly target and clear only the data cells in columns B through E, leaving column F's formulas completely untouched.

When an Excel macro uses commands like `EntireRow.ClearContents`, it wipes all data across every column in that row, which includes destroying your formulas. To resolve this, you must specify the exact block of columns you want to clear dynamically based on the current row number.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Locate Your Macro

In the Project Explorer panel, double-click the module containing your macro, or click Insert > Module to create a new one.

Update the Clear Command

Inside your loop that checks if the column F cell equals "Y", replace the row clearing command with a targeted range command: `Range("B" & cell.Row & ":E" & cell.Row).ClearContents`.

Run and Test

Save your code and run the macro. Only the values in columns B through E will be cleared, while the `=SWITCH(...)` formula in column F remains fully intact.

Use VBA to Clear Specific Cell Ranges (B:E) Instead of the Entire Row

Targeted Clearing: By concatenating the specific column letters ("B" and "E") with the dynamic `cell.Row` property, you strictly limit the macro's action to just those four cells per row.

Solution 2

Hide Values Visually Using Conditional Formatting

If you do not strictly need to delete the underlying data, you can use a custom number format triggered by conditional formatting to make the values invisible.

Automate Data Effortlessly

Use VBA Macros in WPS Spreadsheet to Automate Tasks

WPS Spreadsheet provides robust, built-in support for VBA macros, allowing you to seamlessly run your Excel automation scripts. You can easily target specific ranges and preserve your complex formulas using the exact same VBA codes.

1. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm workbook.
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'Macros' or 'Visual Basic' to access the editor.
3. Run Your Code: Insert or modify your macro code to target specific ranges (e.g., columns B:E) and execute it safely.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats and formula syntax.Includes a built-in VBA editor to write, run, and debug macro code efficiently.Lightweight software with a highly familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does clearing an entire row delete my formulas?

Commands like `EntireRow.ClearContents` or `EntireRow.Delete` act globally on that specific row number. This wipes all content, formatting, or formulas across every single column in that row. To preserve columns, you must programmatically limit your clear command to a specific column range.

Can I use the Undo button after running a VBA macro that clears cells?

No. The standard Undo functionality (Ctrl+Z) in spreadsheet software does not track or reverse actions executed by VBA macros. You should always create a backup copy of your file before testing or running new macro scripts.

What does the custom number format ';;;' do in spreadsheets?

The `;;;` custom number format is a display trick that hides positive numbers, negative numbers, zeros, and text values. It makes the cell look completely empty on the worksheet, even though the original data or formula remains intact and visible in the formula bar.

Excel & Data Problems