VBA & Macro Problems

How to Hide a Row Based on a Cell Value on Another Worksheet using VBA

Huma Ashraf Ch Sep 30, 2026 869 views

Question details

The user wants to use a VBA macro to automatically hide a specific row on a summary worksheet when a dropdown or cell value changes on a different worksheet.

How to Hide a Row Based on a Cell Value on Another Worksheet using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating row visibility in a summary table based on dynamic input data from a separate source worksheet.
Observed behavior: The user needs a Worksheet_Change event script that triggers across worksheets to hide or unhide a row dynamically based on a target cell's value.

Before you start

Ensure your Developer tab is enabled in the ribbon and remember to save your file as a Macro-Enabled Workbook (.xlsm) to preserve the VBA code.

Solution 1Recommended

Use the Worksheet_Change Event to Hide Rows Dynamically

This solution applies a VBA Worksheet_Change event to the source worksheet, monitoring a specific target cell and updating the hidden property of a row on a different summary sheet.

The Worksheet_Change event is triggered automatically whenever a user alters the value of a cell. By placing this code in the specific worksheet module where the input changes, it can control elements on other worksheets without needing manual macro execution.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet.

Locate the Source Worksheet Module

In the Project Explorer pane on the left, find the worksheet where the target cell is located (e.g., 'EquipmentList'). Double-click the worksheet name to open its code module.

Paste the VBA Code

Copy and paste the following code into the module window: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B2")) Is Nothing Then Exit Sub Worksheets("A_Summary").Rows(5).Hidden = (Me.Range("B2").Value = "No") End Sub

Save as a Macro-Enabled Workbook

Close the VBA editor and go to File > Save As. Choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown menu to ensure your code is saved.

Use the Worksheet_Change Event to Hide Rows Dynamically

Modifying Cell References: You can change 'B2' to the cell you want to monitor, and update 'A_Summary' and 'Rows(5)' to match your target worksheet name and row number.

WPS Spreadsheet Macro Support

Automate Worksheets Easily with WPS Spreadsheet Macros

WPS Spreadsheet offers comprehensive support for VBA macros, allowing you to automate repetitive tasks, link worksheets, and control row visibility smoothly and efficiently.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the options menu, and ensure the Developer Tools tab is enabled on your ribbon.
2. Access the VBA Editor: Click the Developer tab and select 'Visual Basic' or press Alt + F11 to launch the macro editor.
3. Insert the Event Code: Double-click your target worksheet in the Project Explorer, paste your Worksheet_Change VBA code, and save the file in .xlsm format.

Fully compatible with Microsoft Excel .xlsm and .xltm formatsBuilt-in VBA editor for smooth macro scripting and debuggingLightweight application with fast performanceFree to use for everyday spreadsheet automation tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my Worksheet_Change VBA event triggering?

This usually happens if macros are disabled in your Trust Center settings, or if the Application.EnableEvents property was accidentally set to False. Ensure macros are enabled and restart the application if necessary.

Can I hide multiple rows based on the cell value?

Yes, you can modify the VBA code to target a range of rows. Instead of Rows(5), you can use Worksheets("A_Summary").Rows("5:10").Hidden to hide rows 5 through 10 simultaneously.

Does this VBA code work in WPS Office?

Yes, WPS Office Spreadsheet supports VBA. You can write and execute Worksheet_Change events exactly as you do in Excel, provided you save your file in the .xlsm macro-enabled format.

Excel & Data Problems