VBA & Macro Problems

Fix Excel VBA Not Hiding Rows Based on Formula Results

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs a VBA macro to automatically hide or display specific rows (e.g., 18:20) when the value of a target cell (e.g., M10) changes based on a calculated formula.

Product: Excel
Device & OS: not provided
Scenario: Automating row visibility based on dynamic formula results in a worksheet.
Observed behavior: The VBA macro fails to execute and update the row visibility when the formula calculates a new result, unlike when values are manually typed into the cell.

Before you start

Ensure that macros are enabled in your Trust Center settings and that your worksheet is not protected, which could block automated row formatting changes.

Solution 1Recommended

Use the Worksheet_Calculate Event

The standard Worksheet_Change event does not fire when a formula result updates. Switching to the Worksheet_Calculate event ensures your code runs whenever the sheet recalculates.

When a cell contains a formula, its value updates through background recalculation rather than a physical change event. Therefore, to trigger a macro when cell M10 calculates a new value, you must place your VBA code inside the Worksheet_Calculate event rather than Worksheet_Change.

Open the VBA Editor

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

Access Worksheet Code

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

Insert Calculate Event

From the top-left drop-down menu in the code window, select 'Worksheet'. From the top-right drop-down, select 'Calculate' to generate the Private Sub Worksheet_Calculate() block.

Add the Logic

Insert your row-hiding logic. For example: Rows("18:20").EntireRow.Hidden = (Range("M10").Value = 0). This evaluates the condition and hides the rows if M10 equals zero.

Performance Optimization: Because the Worksheet_Calculate event fires every time any calculation happens on the sheet, complex logic here can slow down your workbook. Keep the code concise.

Solution 2

Verify Macro Settings and Worksheet Protection

If the macro still doesn't run after updating the event type, security settings or sheet protection might be blocking execution.

Automate Tasks with WPS Spreadsheets

Easily Manage Macros and Automate Workflows with WPS Office

WPS Spreadsheets provides robust support for VBA and macros, allowing you to automate complex tasks like conditionally hiding rows based on cell values just as you would in Microsoft Excel.

1. Open Your Workbook: Launch WPS Spreadsheets and open your macro-enabled file.
2. Access the VBA Editor: Navigate to the Developer tab and click the 'VBA Editor' icon.
3. Implement the Code: Double-click your target worksheet object and paste the Worksheet_Calculate macro script.
4. Save and Run: Save your document as an .xlsm file. The rows will now automatically hide or unhide as your formulas recalculate.

Fully compatible with Microsoft Excel macro-enabled (.xlsm) formatsSupports standard VBA syntax, user forms, and worksheet eventsLightweight application with incredibly fast formula calculation speedsIntuitive Developer tab designed for easy script editing and debugging

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the Worksheet_Change event work for formulas?

The Worksheet_Change event is triggered exclusively by direct user actions, such as manually typing a value, deleting a cell's contents, or pasting data. It is explicitly designed to ignore changes caused by background formula recalculations.

How do I unhide rows manually if my VBA script fails?

Highlight the rows directly above and below the hidden section (for example, select rows 17 and 21), right-click the row numbers on the left-hand sidebar, and choose 'Unhide' from the context menu.

Can I hide rows automatically based on a cell value without using VBA?

No, Excel and other standard spreadsheet programs do not have a built-in feature to dynamically hide rows based on cell values without relying on VBA macros. As an alternative, you can use Conditional Formatting to make the text blend into the background color, but the physical row will remain visible.

Excel & Data Problems