VBA & Macro Problems

How to Hide Excel Rows Based on Operating Hours Using VBA

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants to use a VBA macro to automatically hide spreadsheet rows that fall outside the earliest and latest operating hours calculated from a specific data range, while allowing a small buffer interval to remain visible.

Product: Excel
Device & OS: not provided
Scenario: Managing a timesheet or schedule where non-operating hours need to be hidden to clean up the worksheet view.
Observed behavior: Currently, all hourly rows are visible. The goal is to programmatically identify the minimum and maximum times from one range and hide any rows in another range that do not overlap with this period.

Before you start

Ensure you have saved a backup of your workbook before running new VBA macros, and verify that the Developer tab is enabled in your spreadsheet application so you can access the VBA editor.

Solution 1Recommended

Use VBA to Calculate Time Intervals and Hide Unused Rows

Create a macro that checks the minimum and maximum times in your dataset and loops through your rows, hiding any that do not overlap with this operating period.

By utilizing application functions within VBA, you can extract the earliest and latest times from a specific range (e.g., B47:O47). You can then loop through your target rows (e.g., R:S) to check if the time value falls within this active window.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor. Go to Insert > Module to create a new blank module for your code.

Define the Operating Time Variables

In your script, use application functions like WorksheetFunction.Min(Range("B47:O47")) and WorksheetFunction.Max(Range("B47:O47")) to find the earliest and latest times.

Add a Visibility Buffer

To keep a nonzero interval visible right after the latest time, add a tiny fractional buffer value to the maximum time variable, such as MaxTime = MaxTime + 0.0000001.

Loop Through Target Rows

Create a For loop to iterate through your hourly rows (for instance, checking the time values in columns R and S). Test if the time in each row falls between the calculated minimum and maximum times.

Apply the Hide Property

Within the loop, use an If statement to set Rows(i).EntireRow.Hidden = True for rows that fall outside the calculated operating hours, and False for those within the timeframe.

Time Formats: Ensure your cell values are recognized as proper time serial numbers by the spreadsheet. If they are stored as text strings, the Min and Max functions will not calculate correctly.

Manage Macros Effortlessly

Automate Your Spreadsheets with WPS Office

WPS Spreadsheet provides excellent support for VBA macros, allowing you to automate repetitive tasks like hiding rows based on complex time criteria. It is highly compatible with Microsoft Excel's VBA scripting environment.

1. Download and Install WPS Office: Get the free suite from the official WPS website and launch the WPS Spreadsheet application.
2. Open your Macro-Enabled Workbook: Open your .xlsm file containing your timesheet. WPS Spreadsheet will prompt you to enable macros upon opening.
3. Access the Developer Tab: Navigate to the Developer tab on the top ribbon to easily access the VBA Editor and manage your scripts.
4. Run Your Script: Run your custom VBA code directly within WPS to hide the non-operating hours instantly.

Fully supports VBA and Excel macros (.xlsm formats)Highly compatible with Microsoft Excel formulas and time formattingLightweight application that runs smoothly on any PCFree to download with a familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my rows hiding when I run the macro?

If the rows aren't hiding, verify that the time formats in your cells (e.g., B47:O47 and R:S) are formatted as Time values and not Text. Text strings will cause the Min and Max functions to fail or return a zero value.

How do I unhide the rows later?

You can unhide rows manually by selecting the entire sheet, right-clicking any row header on the left, and selecting 'Unhide'. Alternatively, you can run a simple one-line VBA script: Cells.EntireRow.Hidden = False.

What is the purpose of adding 0.0000001 to the maximum time?

Adding a very small decimal (like 0.0000001) accounts for floating-point calculation errors in VBA and ensures that an interval ending exactly on your latest operating time is not mistakenly hidden by the script.

Can I trigger this macro automatically when my hours change?

Yes, you can place your VBA code inside the Worksheet_Change event for your specific sheet. This setup allows the rows to hide or unhide dynamically whenever you update the operating hours data in your tracked range.

Excel & Data Problems