VBA & Macro Problems

How to Hide Rows When a Date Has Passed using Excel VBA

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to automatically hide specific rows in a worksheet if the date in column A is earlier than today, triggered by selecting a specific value from a data-validation list in cell B1.

Product: Excel
Device & OS: not provided
Scenario: Automating row visibility based on past dates and a dropdown selection using a Worksheet_Change event.
Observed behavior: The current attempt using Select Case with an 'And' condition fails because Select Case doesn't directly support combining complex conditions this way. Rows need to be evaluated and hidden dynamically without causing errors.

Before you start

Ensure your Developer tab is enabled in the ribbon and verify that the dates in column A are formatted as true serial dates, rather than text strings, so the VBA Date function can evaluate them accurately.

Solution 1Recommended

Use a Loop within the Worksheet_Change Event

The most effective way to evaluate each date is by looping through the specific rows when the target cell value changes.

Select Case statements do not directly support the 'And' operator for complex row-by-row evaluations. Instead, use a For loop to check each date individually. Activating the worksheet within the code is unnecessary because the Worksheet_Change event naturally runs on the active sheet.

Open the VBA Editor

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

Access the Worksheet Code Module

In the Project Explorer panel on the left, double-click the name of the worksheet where you want this event to trigger (for example, Sheet1).

Insert the VBA Code

Paste the following script into the code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long If Not Application.Intersect(Range("B1"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Select Case Target.Value Case "Day Total", "" Rows("6:150").AutoFit Case "Week Total" Rows("6:150").RowHeight = 3 Case "Current Day" For r = 6 To 150 Rows(r).EntireRow.Hidden = (Range("A" & r).Value < Date) Next r End Select Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub

Save and Test the Automation

Close the VBA editor. Go to your worksheet and change the dropdown value in cell B1 to 'Current Day'. The rows from 6 to 150 containing dates earlier than today will automatically hide.

Performance Optimization: Disabling 'ScreenUpdating' and 'EnableEvents' before the loop executes prevents screen flickering and significantly speeds up the macro's performance.

Automate with WPS

Easily Manage VBA and Macros in WPS Spreadsheet

WPS Office offers robust support for VBA macros, allowing you to automate tasks like hiding rows based on dates seamlessly. It provides an intuitive environment that is fully compatible with Excel's macro scripts.

1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and install it on your computer.
2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your .xlsm or .xls file containing the date data and data-validation lists.
3. Enable Macros and Run: Navigate to the 'Developer' tab, ensure macros are enabled, and insert or execute your Worksheet_Change event code exactly as you would in Excel.

Highly compatible with Microsoft Excel VBA syntax and .xlsm formatsLightweight architecture ensures fast macro execution for large datasetsFree and robust spreadsheet tool with advanced automation capabilitiesFamiliar interface makes migrating your macro workbooks seamless

Frequently Asked Questions

Why does my Worksheet_Change event trigger an infinite loop?

If your macro modifies cells within the worksheet (like clearing a cell), it can re-trigger the Worksheet_Change event continuously. To prevent this, always include 'Application.EnableEvents = False' at the start of your code block and set it back to 'True' before the macro ends.

Can I use 'Select Case' with multiple conditions instead of a loop?

The Select Case statement in VBA evaluates a single expression against predefined values. It does not support complex logical combinations like checking each cell in a range with an 'And' operator dynamically. A For loop is required to evaluate each row's date individually.

How do I unhide the rows once they are hidden by the macro?

You can unhide rows manually by selecting the entire sheet and right-clicking the row headers to choose 'Unhide'. Alternatively, you can add another Case statement in your VBA code (e.g., Case "Show All") that includes the command 'Rows("6:150").EntireRow.Hidden = False'.

Excel & Data Problems