How to Hide Dynamic Groups of Excel Rows with VBA
Question details
The user needs to hide specific stages and all related data rows dynamically in an imported CRM report using Excel VBA.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Working with an imported CRM report that contains a variable number of rows grouped under stage headings, with each group terminating at a 'Total' row.
- Observed behavior
- The user requires an automated method to hide selected stage groups and their nested names up to the 'Total' row, eliminating the need to manually group and hide rows of varying lengths.
Ensure your CRM report is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings before writing or running any VBA scripts.
Create a VBA Macro with a Boolean Toggle
Use a custom VBA script to automatically scan a specific column, detect the targeted stage heading to start hiding rows, and stop hiding when it reaches the 'Total' row.
This method uses a boolean variable in VBA to track whether rows should be hidden as it loops through a column. Because the script checks each row individually and toggles the hiding action on and off based on cell values, it works perfectly for groups that have a variable number of rows.
Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic', or press ALT + F11 on your keyboard to open the VBA editor.
In the VBA editor, click 'Insert' > 'Module' from the top menu to create a blank script window for your code.
Start your macro by declaring your worksheet (e.g., Set ws = ThisWorkbook.Sheets("Sheet1")), the target scan range (e.g., Set rng = ws.Range("A1:A100")), and the specific stage name you want to hide (e.g., stageToHide = "Stage 1"). Also, declare a boolean variable called 'hideRows' and set it to False initially.
Create a 'For Each' loop to iterate through every cell in your range. Add an 'If' statement: If the cell value matches your target stage, set 'hideRows = True'. Use an 'ElseIf' to check if the cell value equals 'Total', and if so, set 'hideRows = False'.
Inside the loop, immediately after the If/ElseIf block, apply the visibility status by typing 'cell.EntireRow.Hidden = hideRows'. Close the loop with 'Next cell'. Press F5 or click the green 'Run' button on the toolbar to execute the macro.

Automate Your Workflow with WPS Spreadsheet Macros
WPS Spreadsheet supports powerful VBA macros in its advanced versions, allowing you to easily automate repetitive data formatting tasks like hiding dynamic rows in large CRM reports.
- 1. Open Your File: Launch WPS Spreadsheet and open your macro-enabled CRM report (.xlsm).
- 2. Access the Developer Tab: Click on the 'Developer' tab located on the top ribbon.
- 3. Open the VBA Editor: Click on 'Visual Basic Editor' to launch the scripting environment.
- 4. Run Your Macro: Insert a new module, input your dynamic row-hiding script, and click 'Run' to instantly format your data.

Frequently Asked Questions
How can I automatically unhide the rows when I need to see the data again?
You can either select the entire worksheet and manually right-click a row header to select 'Unhide', or write a simple reverse macro that sets 'cell.EntireRow.Hidden = False' for your entire data range.
Can I configure the macro to hide multiple different stage groups at the same time?
Yes. In your VBA 'If' statement, you can use the 'Or' operator to include multiple conditions. For example: If cell.Value = "Stage 1" Or cell.Value = "Stage 2" Then hideRows = True.
How do I make the macro scan the entire column dynamically without setting a fixed range like A1:A100?
Instead of hardcoding the range, you can use a dynamic range definition in VBA. Replace the range setup with 'Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)', which automatically detects the last populated row in column A.




