VBA & Macro Problems

How to Auto-Hide Excel Rows When Cell Changes to Completed Using VBA

Steven Parker Oct 13, 2026 869 views

Question details

The user wants to use a VBA macro to automatically hide a row in a protected worksheet when a dropdown in Column X is set to COMPLETED, and requires a confirmation message to appear before the row is hidden.

How to Auto-Hide Excel Rows When Cell Changes to Completed Using VBA

Product: Excel
Device & OS: not provided
Scenario: Managing task or job lists where completed items need to be securely hidden from view automatically upon status update.
Observed behavior: A VBA script is needed to handle the Worksheet_Change event, trigger a warning prompt, bypass sheet protection temporarily, and successfully hide the row without causing continuous loop errors.

Before you start

Ensure you have the Developer tab enabled in your ribbon and that your spreadsheet is saved as a Macro-Enabled Workbook (.xlsm) so the code runs correctly.

Solution 1Recommended

Use Worksheet_Change Event to Hide Rows

Implement a VBA Worksheet_Change event macro that detects value changes in a specific column, prompts the user, and safely hides the row on a protected worksheet.

This solution utilizes the Worksheet_Change event, meaning the code will automatically execute whenever a cell in the specified range is modified. It uses the Intersect function to ensure the macro only triggers when a cell in Column X is changed.

The code temporarily unprotects the worksheet, turns off Application.EnableEvents to prevent an infinite loop while the macro modifies the sheet, and then safely reprotects the sheet when finished or if an error occurs.

Access the VBA Editor

Open your workbook, right-click on the specific sheet tab at the bottom (e.g., 'Sheet1'), and select 'View Code' from the context menu. This will open the VBA Editor directly to the module for that worksheet.

Insert the VBA Code

Copy and paste the following code into the blank code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim c As Range Set r = Intersect(Me.Range("X:X"), Target) If r Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Me.Unprotect For Each c In r If UCase$(CStr(c.Value)) = "COMPLETED" Then If MsgBox("Are you sure you want to complete this job?", vbOKCancel + vbQuestion, "Confirm Choice") = vbOK Then c.EntireRow.Hidden = True End If End If Next c CleanUp: Me.Protect Application.EnableEvents = True End Sub

Test the Dropdown Validation

Close the VBA Editor and return to your worksheet. Go to Column X and change a cell's value to 'COMPLETED' using your data validation dropdown. A prompt should appear asking for confirmation, and clicking 'OK' will hide the row.

Error Handling Included: The 'On Error GoTo CleanUp' section ensures that if any error happens during execution, your worksheet will be reprotected and event triggers will be turned back on.

Advanced Spreadsheets in WPS Office

Automate and Manage Spreadsheets with WPS Office

WPS Spreadsheet fully supports VBA macros, allowing you to run automated scripts like row-hiding macros exactly as you would in Microsoft Excel, ensuring a smooth and highly productive workflow.

1. Open your Macro Workbook in WPS Office: Launch WPS Spreadsheet and open your existing .xlsm file containing the task list.
2. Enable Macros and Developer Tools: Navigate to the Developer tab. If macros are restricted by default, click on 'Macro Security' to lower the security setting and enable VBA scripts.
3. View and Edit VBA Code: Click the 'VB Editor' button on the Developer ribbon to open the built-in VBA environment, where you can modify or paste the auto-hide row script.
4. Run Seamlessly: Return to the spreadsheet and select 'COMPLETED' from your dropdown to see WPS Spreadsheet automatically process the prompt and hide the row securely.

Seamlessly execute VBA macros and Worksheet eventsHigh format compatibility with Microsoft Excel (.xlsx and .xlsm files)Familiar ribbon interface for a zero-learning-curve transitionFree and lightweight office suite for Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

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

You can manually unhide them by selecting the rows immediately above and below the hidden row, right-clicking the row headers, and choosing 'Unhide'. Alternatively, you can unprotect the sheet and go to Home > Format > Hide & Unhide > Unhide Rows.

Why doesn't the macro run when I type COMPLETED?

Ensure that macros are enabled in your Trust Center or Macro Security settings. Additionally, verify that Application.EnableEvents was not left turned off due to a previous macro error. If so, you can reset it by typing 'Application.EnableEvents = True' in the Immediate Window of the VBA Editor.

How do I adapt this code for a different column?

In the provided VBA code, locate the line that says 'Set r = Intersect(Me.Range("X:X"), Target)'. Change the 'X:X' portion to the letter of the column you wish to monitor, such as 'Z:Z' or 'C:C'.

Will this code work if my sheet is protected with a password?

The current script uses 'Me.Unprotect' and 'Me.Protect' which works for sheets without passwords. If your sheet has a password, change those lines to 'Me.Unprotect Password:="YourPassword"' and 'Me.Protect Password:="YourPassword"'.

Excel & Data Problems