VBA & Macro Problems

How to Automatically Hide Excel Rows When Completion Reaches 100%

Chanuka Geekiyanage Oct 10, 2026 869 views

Question details

The user wants to automatically hide rows in a project tracking spreadsheet when the completion status reaches 100%.

Automatically Hide Excel Rows When Completion Reaches 100%

Product: Excel
Device & OS: not provided
Scenario: Tracking project progress in a spreadsheet where completed projects need to be hidden automatically to maintain a clean and focused task list.
Observed behavior: Currently, rows remain visible even after their status changes to 100% completion, requiring the user to manually filter or hide the rows.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) so the VBA code can run, and identify the exact column letter where your completion percentage is recorded.

Solution 1Recommended

Use a VBA Worksheet_Change Event to Auto-Hide Rows

This automated method monitors your completion column in real-time and hides the entire row the moment a 100% value is entered.

By utilizing the Worksheet_Change event in VBA, you can instruct Excel to constantly watch a specific column. When it detects that a cell's value has changed to 100% (or the numeric equivalent of 1), it triggers an action to hide that specific row immediately.

Access the VBA Editor

Right-click on the specific sheet tab at the bottom of your Excel window and select 'View Code' from the context menu.

Set Up the Worksheet Change Event

In the code window, use the left dropdown at the top to select 'Worksheet' and the right dropdown to select 'Change'.

Define the Target Column and Condition

Write a script that checks if the modified cell is in your specific percentage column (e.g., using Intersect) and evaluate if the Target.Value equals 1 (for 100%).

Apply the Hide Row Command

Inside your conditional statement, add the command 'Target.EntireRow.Hidden = True' to hide the row when the condition is met.

Test the Automation

Close the VBA editor, return to your worksheet, and type '100%' into the target column to verify the row disappears.

Use a VBA Worksheet_Change Event to Auto-Hide Rows

Percentage Value Formatting: If your cells are formatted as percentages, Excel stores '100%' as the number '1'. Ensure your VBA code checks for the value '1', not '100', otherwise the macro will not trigger.

Solution 2

Filter Out Completed Rows (Non-VBA Alternative)

If you prefer not to use macros, you can use Excel's built-in filtering tool to quickly hide 100% completed tasks in bulk.

Advanced Automation

Automate Project Tracking with WPS Spreadsheet Macros

WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly run Worksheet_Change events to hide completed rows, just like in Microsoft Excel.

1. Enable Macros: Open WPS Spreadsheet, navigate to the Developer tab, and click 'Macros' to allow macro execution.
2. Access the Code Editor: Click 'Visual Basic' or right-click your sheet tab to select 'View Code'.
3. Insert the Script: Paste your Worksheet_Change VBA script into the module to handle the 100% completion logic.
4. Save as XLSM: Save your document as a Macro-Enabled Workbook to keep your row-hiding automation active.

Fully compatible with Microsoft Excel VBA macros and .xlsm formats.Lightweight architecture ensures scripts run smoothly without lag.Free built-in developer tools for writing and debugging VBA code.Cross-platform compatibility for seamless workflow management.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro hiding the rows when I type 100%?

This usually happens because of value formatting. If the cell is formatted as a percentage, the application stores '100%' as the underlying number '1'. Ensure your VBA code condition checks for 'Target.Value = 1' rather than '100'.

How can I unhide the rows once they are automatically hidden?

You can unhide them manually by highlighting the rows immediately above and below the hidden row, right-clicking the row numbers on the left side of the screen, and selecting 'Unhide'.

Will this Worksheet_Change macro slow down my spreadsheet?

If properly written, a Worksheet_Change macro checking a single column will not noticeably slow down your spreadsheet. However, be sure to use 'Intersect' to limit the code's execution only to your specific completion column.

Can I apply this auto-hide macro to multiple columns?

Yes, you can modify the Intersect function in your VBA script to include a larger range or multiple columns if you track completion percentages in more than one place.

Excel & Data Problems