How to Use Worksheet_Calculate to Hide Rows After Slicer Changes in Excel
Question details
The user needs to dynamically hide or unhide rows based on a formula recalculation triggered by a PivotTable slicer selection.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A formula in cell C25 updates when a PivotTable slicer is clicked, and rows between that cell and a designated table ('Table1') need to be hidden dynamically.
- Observed behavior
- The standard Worksheet_Change event does not trigger upon formula recalculations, meaning the row visibility does not update automatically when the slicer changes the formula result.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Trust Center settings before implementing this VBA code.
Use the Worksheet_Calculate Event in the Target Module
Since the Worksheet_Change event doesn't detect formula updates, use the Worksheet_Calculate event to adjust row visibility whenever the sheet recalculates.
The Worksheet_Calculate event is triggered whenever any formula on the active sheet recalculates. Because this event does not have a Target argument to identify exactly which cell changed, the macro must evaluate the specific cells every time it runs.
To prevent the macro from triggering an infinite loop while hiding and unhiding rows, we temporarily disable background events using Application.EnableEvents = False during execution.
Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications editor.
In the Project Explorer panel on the left side, double-click the specific worksheet module where your reference cell (C25) and Table1 are located (e.g., Sheet1).
Paste the required Private Sub Worksheet_Calculate() code into the module's code window.
Close the VBA Editor and return to your spreadsheet. Click a selection in your PivotTable Slicer to test if the rows correctly hide or unhide based on the updated formula.
Run Your VBA Macros Seamlessly in WPS Office
WPS Spreadsheet offers built-in support for VBA macros, allowing you to utilize events like Worksheet_Calculate to automate your dynamic reports, PivotTables, and Slicers exactly as you would in Microsoft Excel.
- 1. Open Your Workbook in WPS: Launch WPS Spreadsheet and open your Macro-Enabled Workbook (.xlsm).
- 2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' to open the code editor.
- 3. Edit the Worksheet Module: Double-click the target sheet in the Project Explorer and insert your Worksheet_Calculate code.
- 4. Run and Automate: Save your changes and interact with your Slicer to see rows dynamically hide and unhide.
Frequently Asked Questions
Why doesn't Worksheet_Change run when my formula updates?
The Worksheet_Change event is exclusively triggered by direct physical inputs or edits to a cell by a user or another macro. It does not detect changes caused by a formula recalculating. For formula-driven cell changes, you must use the Worksheet_Calculate event.
How do I prevent infinite loops when hiding rows via Worksheet_Calculate?
Use Application.EnableEvents = False at the beginning of your code to temporarily disable event triggers. Always include an error handler (like On Error GoTo CleanUp) to ensure Application.EnableEvents = True is executed at the end, returning Excel to its normal state.
Does Worksheet_Calculate tell me which specific formula updated?
No, unlike Worksheet_Change, the Worksheet_Calculate event does not have a Target parameter. It triggers whenever any formula on the sheet recalculates, so your code must evaluate specific cells (like C25) manually within the script to determine the appropriate action.
Can I use this method to hide columns instead of rows?
Yes, you can easily adapt the logic. Instead of referencing ws.Rows().Hidden = True, calculate the column index and use ws.Columns().Hidden = True to dynamically hide columns when the slicer changes.
