VBA & Macro Problems

How to Automatically Hide an Excel Row When Column Says Complete

Khadija Khan Oct 10, 2026 869 views

Question details

The user needs a way to automatically hide entire rows in a spreadsheet when the text Complete is entered into Column E.

How to Automatically Hide an Excel Row When a Cell Says Complete

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Managing dynamic task lists or project trackers where completed items need to be hidden automatically to declutter the active worksheet.
Observed behavior: The user wants the spreadsheet to instantly hide the row upon the cell value changing to Complete, eliminating the need to manually select and hide completed rows.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macro security settings are configured to allow scripts to run on your device.

Solution 1Recommended

Use a Worksheet_Change VBA Macro to Hide Rows

This method utilizes a VBA event handler that continuously monitors Column E and automatically hides the corresponding row the moment Complete is typed.

By placing the VBA code directly into the specific worksheet module, the script can detect changes in real-time. The code is designed to temporarily disable screen updating and events during execution, preventing lag and infinite loops when processing multiple cells at once.

Access the Worksheet Code Module

Right-click the specific worksheet tab (for example, Sheet1) at the bottom of your spreadsheet window and select View Code from the context menu to open the VBA Editor.

Paste the Auto-Hide Macro Script

In the blank code window that appears, paste the following VBA code: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cel As Range If Target.CountLarge > 1000 Then Exit Sub Set rng = Intersect(Me.Range("E:E"), Target) If Not rng Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each cel In rng If cel.Value = "Complete" Then cel.EntireRow.Hidden = True Next cel Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub

Save as a Macro-Enabled Workbook

Close the VBA Editor. Go to File > Save As, and choose Excel Macro-Enabled Workbook (*.xlsm) from the file format dropdown. This ensures your macro is preserved when you close the file.

Use a Worksheet_Change VBA Macro to Hide Rows

Case Sensitivity: The VBA script is case-sensitive. The row will only hide if you type Complete with a capital C. If you want it to trigger for complete or COMPLETE, you can modify the code to check UCase(cel.Value) = "COMPLETE".

Efficient Spreadsheet Management

Automate Your Worksheets Easily with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, enabling you to automate repetitive tasks like dynamically hiding rows based on cell status. It offers a seamless environment for running and writing complex scripts.

1. Open Your Spreadsheet in WPS Office: Launch WPS Office and open your task list or project tracker where you want to automate row hiding.
2. Enable the Developer Tools: Navigate to the Developer tab on the top ribbon. If macros are disabled, click Macro Security to enable them.
3. Insert the VBA Code: Right-click your active sheet tab, select View Code, and paste the auto-hide Worksheet_Change script.
4. Save as an .xlsm Format: Click File > Save As and select the Macro-Enabled Workbook (.xlsm) format to keep your automation active.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) macro-enabled formats.Built-in Developer tab for seamless VBA and macro script management.Lightweight architecture ensures fast execution of automation scripts.Familiar user interface requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

How do I unhide the rows after the macro has hidden them?

To unhide rows, click the top-left triangle between the row numbers and column letters to select the entire worksheet. Then, right-click any row number and select Unhide from the menu.

Why isn't the macro triggering when I type Complete?

First, ensure macros are enabled in your security settings. Second, confirm that you pasted the code into the specific worksheet module (e.g., Sheet1) and not into a standard module (like Module1). Finally, check that your spelling and capitalization exactly match the word Complete in the code.

Can I modify the macro to monitor a different column, like Column H?

Yes. In the provided VBA code, locate the line that says Set rng = Intersect(Me.Range("E:E"), Target). Change "E:E" to "H:H" or whichever column letter you wish to monitor.

Will this macro work if I paste Complete into multiple cells at once?

Yes, the script is specifically designed with a For Each loop to handle scenarios where users paste or fill data into multiple cells at the same time, preventing the macro from crashing.

Excel & Data Problems