VBA & Macro Problems

How to Automatically Hide Excel Rows When a Drop-Down Value is Selected

Maira Mehtab Sep 27, 2026 869 views

Question details

The user wants to automatically hide specific rows in an Excel table when a specific value, such as 'Completed', is selected from a drop-down list.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing task lists or dynamically expanding tables where completed items need to be hidden from the active view automatically.
Observed behavior: Excel lacks a built-in conditional formatting rule to hide entire rows based on cell values, requiring workarounds like VBA, Power Automate, or manual filtering.

Before you start

Before applying VBA macros to your worksheet, ensure you save your file as an Excel Macro-Enabled Workbook (.xlsm) to preserve the code.

Solution 1Recommended

Use a Worksheet_Change VBA Macro

The most seamless way to automatically hide rows based on a drop-down selection is by using a VBA event trigger.

By utilizing the Worksheet_Change event, Excel will actively monitor your drop-down column. Whenever a value changes to your target word, the script instantly hides the corresponding row.

Open the VBA Editor

Right-click on the sheet tab at the bottom of your workbook where your table is located, and select 'View Code' to open the VBA Editor.

Insert the Event Macro

In the code window, define a 'Private Sub Worksheet_Change(ByVal Target As Range)' macro. Write a condition that checks if the Target column matches your drop-down column, and if Target.Value equals 'Completed', apply 'Target.EntireRow.Hidden = True'.

Test the Drop-down

Close the VBA Editor and return to your worksheet. Select 'Completed' from a drop-down list to verify that the row automatically hides.

Macro Security: Make sure you have macros enabled in your Excel Trust Center settings, otherwise the Worksheet_Change event will not trigger.

Solution 2

Filter Out Completed Rows Manually

If you prefer not to use VBA or macros, you can use Excel's standard filtering tools to temporarily hide rows that contain a specific drop-down value.

Efficient Spreadsheet Management

Easily Manage Data and Macros with WPS Spreadsheet

WPS Office provides excellent compatibility with Microsoft Excel, fully supporting VBA macros (.xlsm) and advanced filtering, making it easy to automate row hiding and manage large datasets.

1. Open Your Workbook: Launch WPS Spreadsheet and open your existing task list or dynamic table.
2. Access the VBA Editor: Go to the Developer tab and click 'Visual Basic' to insert your auto-hide row macro directly into the worksheet module.
3. Save and Run: Save the file in the .xlsm format to keep the macro active and enjoy automated row hiding.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) file formats.Built-in VBA editor to create and run macros seamlessly.Advanced filtering and drop-down list support for effortless data management.Lightweight, free alternative that runs smoothly on Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Can conditional formatting hide an entire row in Excel?

No, conditional formatting can only change the appearance of cells, such as font color, background color, or borders. It cannot change row height or hide entire rows. You must use VBA or data filtering for that functionality.

How do I unhide rows that were hidden by a VBA macro?

To unhide rows manually, select the row headers above and below the hidden rows, right-click, and choose 'Unhide'. Alternatively, you can modify your VBA script to include a toggle or an unhide function.

Will the VBA macro work if I add new rows to my dynamically expanding table?

Yes, if you use the Worksheet_Change event in VBA, it automatically detects modifications in the specified column. When a new row is added and the drop-down value is changed to the target word, the macro will hide the new row just like the old ones.

Excel & Data Problems