VBA & Macro Problems

How to Hide Excel Rows When Cell is Greater Than Zero (VBA)

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to automatically hide rows 18 through 20 in an Excel worksheet whenever the value in cell M10 is greater than zero, while ensuring the automation works even if the worksheet is password-protected.

Product: Excel
Device & OS: not provided
Scenario: Automating row visibility based on a specific numeric condition applied to a manually entered cell value in a protected workbook.
Observed behavior: When the user manually updates cell M10 with a positive number, rows 18 to 20 should be hidden without triggering a protection error. If the value is zero or less, the rows should remain visible.

Before you start

Ensure you have the Developer tab enabled on your Excel ribbon and that macros are allowed to run in your security settings.

Solution 1Recommended

Use the Worksheet_Change VBA Event

This VBA solution triggers automatically when a user manually enters a value into a specific cell, seamlessly unprotecting the sheet, updating row visibility, and reprotecting it.

This method utilizes the Worksheet_Change event, which is the most efficient approach when the target cell is updated via direct manual data entry.

If your target cell is updated by a formula rather than a manual entry, the Worksheet_Change event will not trigger. In that specific case, you would need to use the Worksheet_Calculate event instead.

Open the VBA Editor

Right-click the specific worksheet tab at the bottom of your Excel window and select 'View Code'. This will open the Visual Basic for Applications (VBA) Editor for that specific sheet.

Paste the Macro Code

In the code window, paste the following script: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "M10" Then Me.Unprotect Password:="TL1234" Rows("18:20").Hidden = (Target.Value > 0) Me.Protect Password:="TL1234" End If End Sub

Customize the Parameters

Update the target cell ("M10"), the rows to hide ("18:20"), and the password string ("TL1234") in the script to match the exact requirements and protection password of your worksheet.

Test the Automation

Close the VBA Editor and return to your Excel worksheet. Type a number greater than zero in cell M10 and press Enter; the specified rows will hide instantly.

Automatic Unhiding: The statement `(Target.Value > 0)` is a boolean expression. If you enter 0 or a negative number, it evaluates to False, which will automatically unhide the rows.

Advanced Spreadsheet Features

Automate Your Spreadsheets Seamlessly with WPS Office

WPS Office provides robust built-in support for VBA and macros, allowing you to run complex automation tasks, such as hiding specific rows based on cell conditions, effortlessly.

1. Open Your Workbook in WPS: Launch WPS Spreadsheets and open your .xlsm or .xlsx file.
2. Enable the Developer Tab: Go to the settings or options menu to ensure the Developer tab is checked and visible on your main ribbon.
3. Access the VB Editor: Navigate to the Developer tab and click 'VB Editor' to paste or modify your Worksheet_Change scripts.
4. Save and Run: Save your document as a macro-enabled workbook (.xlsm). The row-hiding script will automatically trigger whenever the target cell is modified.

Fully compatible with Microsoft Excel formats, including macro-enabled workbooks (.xlsm)High-performance architecture that executes VBA scripts quickly and reliablyFamiliar user interface making it easy to access the Developer tab and VB EditorFree and lightweight alternative to standard premium office suites

microsoft office alternative - wps office

Frequently Asked Questions

What if the target cell contains a formula instead of a manually entered value?

If the cell is updated via a formula, the Worksheet_Change event does not detect the value change. You must use the Worksheet_Calculate event in your VBA script to check the resulting formula value and hide the rows accordingly.

Why do I get a runtime error when the macro tries to hide the rows?

This error typically occurs if the worksheet is locked for editing. You must include the `Me.Unprotect Password:="YourPassword"` command before modifying the row visibility, and follow it with `Me.Protect Password:="YourPassword"` to re-lock the sheet.

Will the hidden rows automatically reappear if I change the cell value back to zero?

Yes. The VBA logic `Rows("18:20").Hidden = (Target.Value > 0)` dynamically evaluates the cell's value. If the value becomes zero or less, the condition evaluates to False, which effectively unhides the rows.

Excel & Data Problems