VBA & Macro Problems

How to Automatically Clear Related Excel Cells When a Value is Deleted

Steve KSteve K Nov 10, 2026 868 views

Question details

The user wants to automatically clear specific related cells in Excel when a primary value is deleted, without removing underlying formulas, and applying this across multiple worksheets.

How to Automatically Clear Related Excel Cells When a Value is Deleted
Product
Excel
Device & OS
not provided
Scenario
Managing dynamic data entry where deleting a primary input should reset dependent cells across the workbook.
Observed behavior
Currently, deleting a value leaves related cells populated or requires manual clearing, which is inefficient and prone to errors.
Before you start

Ensure you have the Developer tab enabled in your Excel ribbon to access the VBA editor. Remember to save your file as a Macro-Enabled Workbook (.xlsm) so your code is preserved.

Solution 1Recommended

Use the Workbook_SheetChange Event in VBA

This method uses a workbook-level event to monitor changes across all sheets and automatically clear dependent cells when a target cell is emptied.

By placing the code in the ThisWorkbook module, the macro will trigger on any worksheet within the file. Disabling events temporarily during the clearing process prevents the macro from triggering itself in an endless loop.

1
Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor in Excel.

2
Access ThisWorkbook Module

In the Project Explorer pane on the left side of the screen, double-click on 'ThisWorkbook'.

3
Insert the VBA Code

Select 'Workbook' from the left dropdown and 'SheetChange' from the right dropdown at the top of the code window. Write an IF statement to check if the target cell is empty (e.g., If Target.Value = "" Then).

4
Disable Events and Clear Cells

Add Application.EnableEvents = False before your clearing logic (e.g., Target.Offset(0, 1).ClearContents) to prevent loops, then immediately re-enable events with Application.EnableEvents = True.

5
Save as XLSM

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown to ensure your macro is saved.

Use the Workbook_SheetChange Event in VBA
Preserving Formulas: Use the .ClearContents method instead of .Clear to ensure that only the data is removed while keeping cell formatting and formulas intact.
WPS Spreadsheet VBA Support

Use WPS Spreadsheet to Run VBA Macros Seamlessly

WPS Office provides robust support for VBA macros, allowing you to automate tasks like clearing related cells just as you would in Microsoft Excel. It's a lightweight, highly compatible alternative that handles .xlsm files effortlessly.

  1. 1. Open your Workbook in WPS: Launch WPS Spreadsheet and open your existing .xlsm file.
  2. 2. Enable Macros: Click on the 'Developer' tab in the ribbon and ensure macros are enabled in the macro security settings.
  3. 3. Access the VBA Editor: Click the 'Visual Basic' button on the Developer ribbon to view or edit your Workbook_SheetChange code.
Fully compatible with Microsoft Excel .xlsm and .xlsx formatsBuilt-in VBA editor for writing and executing macrosLightweight application with fast loading timesFree to use with a familiar, intuitive interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel crash when the macro clears the related cells?

This usually happens due to an infinite loop. When the macro clears a cell, it triggers the SheetChange event again. To fix this, wrap your clearing code with Application.EnableEvents = False and Application.EnableEvents = True.

How do I keep formulas when clearing cells with VBA?

Instead of using Range.Clear which removes everything including formatting and formulas, use Range.ClearContents. If you only want to clear constants and leave formulas, you can use Range.SpecialCells(xlCellTypeConstants).ClearContents.

Can I restrict this macro to specific worksheets only?

Yes. Inside the Workbook_SheetChange event, you can add an IF statement to check the sheet name, such as If Sh.Name = "Sheet1" Then, so the code only executes on the desired sheets instead of the entire workbook.

Why isn't my VBA code running when I delete a value?

Ensure that macros are enabled in your Trust Center settings. Also, check that your code is placed in the 'ThisWorkbook' module rather than a standard module, and that Application.EnableEvents hasn't been accidentally set to False from a previous error.