VBA & Macro Problems

How to Use VBA to Fill Missing Hourly Time Intervals in Excel

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs an Excel VBA macro to populate missing hourly timestamps in an activity report, covering the full 08:00 to 18:00 schedule.

Product: Excel
Device & OS: not provided
Scenario: Completing an activity report by filling in missing hourly time intervals, including gaps before the first entry and after the last entry.
Observed behavior: The activity report contains gaps in hourly timestamps that need to be automatically filled via VBA from 08:00 to 18:00, while preserving the header in row 1.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that your timestamp data starts in row 2, keeping row 1 reserved strictly for column headers.

Solution 1Recommended

Create a VBA Macro to Insert Missing Hourly Timestamps

Use this VBA approach to iterate through your data starting from row 2, intelligently inserting missing hourly intervals between 08:00 and 18:00.

This solution involves writing a macro that evaluates the time values in your designated column. It calculates the difference between consecutive rows and inserts a new row if the gap exceeds one hour.

The script also ensures that the timeline strictly starts at 08:00 and ends at 18:00 by adding necessary intervals before the first timestamp and after the final timestamp.

Open the VBA Editor

Press the ALT + F11 shortcut keys in Excel to open the Visual Basic for Applications (VBA) Editor window.

Insert a New Module

Click 'Insert' in the top menu bar, then select 'Module' to create a blank workspace for your macro code.

Draft the Loop Logic

Write a script starting with 'For i = 2 To LastRow'. Starting at row 2 is crucial because row 1 contains headers used for filtering. Use the 'TimeValue' function to establish your 08:00 and 18:00 boundaries.

Insert Missing Rows

Within the loop, add an IF statement that checks if the difference between cells is greater than 1 hour. If true, use 'Rows(i).Insert Shift:=xlDown' to add a new row and populate it with the missing hourly timestamp.

Run the Macro

Once the script handles gaps, as well as the 08:00 start and 18:00 end limits, press F5 or click the 'Run' button (green triangle) to execute the macro and fill your report.

Data Formatting Requirement: Make sure the column containing your timestamps is properly formatted as 'Time' or 'Custom (hh:mm)' so that the VBA TimeValue comparisons function accurately.

Advanced Spreadsheet Automation

Use WPS Spreadsheet to Run VBA Macros Seamlessly

WPS Office provides a highly compatible built-in VBA editor, enabling you to execute custom macros like time-interval filling without requiring Microsoft Excel. It is lightweight, fast, and fully supports standard macro workflows.

1. Open Your Report in WPS Spreadsheet: Launch WPS Office and open your activity report workbook containing the timestamp data.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor' to launch the macro interface.
3. Insert and Run the Code: Click 'Insert' > 'Module', paste your missing-hour timestamp macro code, and press F5 to automatically fill the gaps between 08:00 and 18:00.

Fully compatible with Microsoft Excel VBA scripts (.xlsm formats).Built-in macro editor for automating repetitive data entry tasks.Lightweight installation with rapid execution speeds.Free and highly compatible with standard Excel formulas and functions.

microsoft office alternative - wps office

Frequently Asked Questions

Why must the VBA loop start processing from row 2?

Row 1 typically contains column headers used for sorting, filtering, and data identification. Starting your VBA loop from row 2 ensures that your headers are not overwritten, shifted, or incorrectly processed as time values by the macro.

How do I define the 08:00 and 18:00 time limits in my VBA code?

You can define precise time limits in VBA using the TimeValue function. For instance, declare variables using TimeValue("08:00:00") and TimeValue("18:00:00"). The macro can then compare the first and last rows of your dataset against these variables to insert any missing outside boundaries.

Why is my macro failing to insert rows?

This usually happens if macros are disabled in your security settings, the worksheet is protected, or the timestamp cells are formatted as text rather than valid time values. Check the Developer tab settings to ensure macros are enabled and verify your cell formatting.

Excel & Data Problems