VBA & Macro Problems

How to Hide Dynamic Groups of Excel Rows with VBA

Camila MilosovichCamila Milosovich Oct 7, 2026 871 views

Question details

The user needs to hide specific stages and all related data rows dynamically in an imported CRM report using Excel VBA.

How to Hide Dynamic Groups of Excel Rows with 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic', or press ALT + F11 on your keyboard to open the VBA editor.

2
Insert a New Module

In the VBA editor, click 'Insert' > 'Module' from the top menu to create a blank script window for your code.

3
Declare Variables and Range

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.

4
Set Up the Logic Loop

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'.

5
Apply Row Visibility and Run

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.

Create a VBA Macro with a Boolean Toggle
Label Consistency: Ensure that your target stage names and the word 'Total' are spelled perfectly consistently throughout the CRM report, as VBA relies on exact text matches to trigger the hiding logic.
Efficient Data Management

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. 1. Open Your File: Launch WPS Spreadsheet and open your macro-enabled CRM report (.xlsm).
  2. 2. Access the Developer Tab: Click on the 'Developer' tab located on the top ribbon.
  3. 3. Open the VBA Editor: Click on 'Visual Basic Editor' to launch the scripting environment.
  4. 4. Run Your Macro: Insert a new module, input your dynamic row-hiding script, and click 'Run' to instantly format your data.
Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro formatsFamiliar Developer tab and Visual Basic editing environmentLightweight design ensures fast processing of complex CRM reportsIntuitive ribbon interface identical to standard spreadsheet software
microsoft office alternative - wps office

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.