VBA & Macro Problems

How to Create an Excel VBA Macro to Hide or Group Rows by Value

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to create an Excel VBA macro that automatically evaluates values in column H and hides or groups the rows if the value exceeds -500, as manually recorded macros fail with dynamic daily data.

Product: Excel
Device & OS: not provided
Scenario: Automating the hiding or grouping of rows based on dynamic daily data, where a recorded macro based on mouse clicks is no longer reliable.
Observed behavior: Recorded mouse-click macros fail when daily data changes, necessitating a programmable VBA solution that systematically evaluates row values.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and verify that the Developer tab is enabled in your ribbon so you can access the VBA Editor.

Solution 1Recommended

Create a VBA Macro to Hide Rows Based on Column Values

Use a custom VBA script to loop through cells in column H and dynamically hide the entire row if the value exceeds -500.

A standard recorded macro relies on static cell references, which causes it to fail when data expands or changes daily. By writing a script that loops through a specific range, the macro adapts to the current data state.

Open the VBA Editor

Navigate to the Developer tab on the Excel ribbon and click 'Visual Basic' to launch the VBA Editor.

Insert a New Module

In the VBA Editor, click 'Insert' from the top menu and select 'Module' to create a blank script window.

Write the Evaluation Loop

Define your macro and write a 'For Each' loop targeting the range in column H. Add an 'If' statement to check if the cell value is greater than -500.

Hide the Target Rows

Inside the 'If' statement, add the command 'cell.EntireRow.Hidden = True' to hide any row that meets the condition.

Run the Macro

Close the VBA Editor, return to your worksheet, click 'Macros' on the Developer tab, select your new macro, and click 'Run'.

Testing with Sample Data: Always test your new macro on a sample workbook containing representative data before running it on critical files. Be sure to remove any sensitive information if you plan to share the workbook for troubleshooting.

Solution 2

Use VBA to Group and Collapse Rows by Value

Instead of completely hiding rows, use VBA to group rows together via the Outline feature, allowing users to easily expand or collapse them manually.

Efficient VBA Macro Support

Automate Your Worksheets with WPS Spreadsheet

WPS Office fully supports VBA macros, allowing you to run, edit, and create complex scripts like hiding rows by value just as you would in Microsoft Excel.

1. Open Your Macro Workbook: Open your .xlsm file in WPS Spreadsheet.
2. Access Developer Tools: Navigate to the Developer tab. If it is not visible, enable it from the WPS settings menu.
3. Open the VBA Editor: Click 'VBA Editor' on the ribbon to open the scripting environment.
4. Paste the Macro Code: Insert a new module and paste your VBA macro code designed to hide or group rows in column H.
5. Run the Macro: Click 'Run' to execute the script and automatically format your dynamic daily data.

Seamless compatibility with Microsoft Excel .xlsm and .xlsb formatsBuilt-in Developer tools for editing and debugging VBA macrosLightweight application that runs macros quicklyFree to download with premium advanced features

microsoft office alternative - wps office

Frequently Asked Questions

Why does my recorded macro fail when my daily data changes?

Recorded macros capture literal mouse clicks and static cell references. When the amount of daily data grows or shifts, the recorded steps point to the old cell locations, causing the macro to format the wrong rows or fail entirely. A VBA script that evaluates the data dynamically solves this.

How do I unhide rows that were hidden by the VBA macro?

You can select the visible rows directly above and below the hidden section, right-click, and choose 'Unhide'. Alternatively, you can write a simple macro with the command 'Cells.EntireRow.Hidden = False' to unhide all rows in the active worksheet instantly.

What happens if column H contains text or errors instead of numbers?

If your macro attempts to compare text or an error value to the number -500, it will likely return a 'Type Mismatch' error and halt the script. You can prevent this by wrapping your condition in an 'If IsNumeric(cell.Value)' check before comparing the value.

Do VBA macros work in WPS Office?

Yes, WPS Office includes a robust VBA environment. You can open existing Excel Macro-Enabled Workbooks directly in WPS Spreadsheet and continue using, editing, or creating new VBA macros seamlessly.

Excel & Data Problems