VBA & Macro Problems

How to Automatically Hide Excel Rows When a Formula Result Changes

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to automatically hide or show specific rows based on whether a cell evaluates to 'Yes' or 'No', but their existing Worksheet_Change macro fails to trigger when the value is updated by a formula.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating row visibility dynamically based on formula results using VBA macros.
Observed behavior: The Worksheet_Change event successfully triggers on manual cell edits but fails to execute when the target cell's value changes due to a formula recalculation.

Before you start

Ensure you have Developer tools enabled in your spreadsheet software and always save a backup copy of your workbook before editing VBA macro code.

Solution 1Recommended

Use the Worksheet_Calculate Event Macro

Replace the Worksheet_Change event with Worksheet_Calculate to successfully detect cell value changes caused by formula recalculations.

The Worksheet_Change event only monitors physical user inputs or direct script changes. To trigger an action when a formula updates, you must rely on the Worksheet_Calculate event, which fires every time the sheet recalculates.

Open the VBA Editor

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

Select the Target Worksheet

In the Project Explorer pane on the left, double-click the specific worksheet containing your formula (e.g., Sheet1).

Insert the Calculate Event

From the left dropdown menu at the top of the code window, select 'Worksheet'. From the right dropdown, select 'Calculate'.

Write the Conditional Logic

Inside the Worksheet_Calculate procedure, write an IF statement to check the value of your formula cell (e.g., Range("B2").Value) and set the target row's Hidden property (e.g., Rows("2:2").EntireRow.Hidden = True) based on the result.

Combine Multiple Cell Logic: A worksheet can only have one Worksheet_Calculate event. If you need to monitor multiple formula cells, combine all your IF statements and logic into that single procedure to avoid duplicate declaration errors.

Advanced VBA Macro Support

Automate Row Visibility Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports Excel VBA macros in its advanced editions, allowing you to utilize Worksheet_Calculate events to easily hide or show rows based on dynamic formula results.

1. Open Your Workbook: Launch WPS Office and open your macro-enabled workbook (.xlsm or .xls).
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon and click 'Visual Basic' (or press ALT + F11).
3. Implement the Macro: Navigate to your target sheet module and insert the Worksheet_Calculate VBA code to manage row visibility.
4. Save as Macro-Enabled: Save your document as a Macro-Enabled Workbook to ensure your automation logic is preserved.

Highly compatible with Microsoft Excel VBA macros and scriptsLightweight application with fast and efficient formula recalculationIncludes comprehensive Developer tools for custom automationFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Worksheet_Change trigger on formula updates?

The Worksheet_Change event is specifically designed to fire only when a user manually edits a cell or when a VBA script explicitly changes a cell's value. It does not monitor or detect when a formula recalculates a new result.

Can I monitor multiple formula cells with Worksheet_Calculate?

Yes, but you must include all your conditional checks within a single Worksheet_Calculate block. Excel only allows one instance of this event procedure per worksheet module.

How do I fix the 'duplicate declaration' error in my macro?

This error occurs when you try to declare the same variable name or use multiple Worksheet_Calculate procedures in the same sheet. Ensure you only have one Worksheet_Calculate block and use unique variable names for different target cells or loops.

Excel & Data Problems