如何在Excel中为每行生成起止日期区间内的所有日期？

Hey there! Dealing with 16k rows of date ranges can feel overwhelming, but we’ve got two solid methods to expand those start/end dates into individual rows for every date in each range. Let’s dive in:

Power Query is built for handling big data without slowing down Excel—perfect for your 16k rows. Here’s how to use it:

• First, select your data range (including headers) and go to the Data tab > From Table/Range. This will open the Power Query Editor.
• Next, add a custom column: Go to Add Column > Custom Column. In the formula box, paste this (replace StartDate and EndDate with your actual H and I column headers, e.g., [Start Date] and [End Date]):

  {[StartDate]..[EndDate]}
  

  This creates a list of all dates between your start and end date for each row.
• Now, expand that list into rows: Click the small arrow on the right side of your new custom column header, then select Expand to New Rows.
• Finally, go to Home > Close & Load to bring the expanded data back into a new Excel table.

This method is fast, non-destructive (your original data stays intact), and handles even the 365-day ranges smoothly.

If you prefer using macros, this VBA script will loop through each row, expand the date range, and output everything to a new sheet:

Sub ExpandDateRanges()
    Dim wsSource As Worksheet, wsOutput As Worksheet
    Dim lastRow As Long, i As Long, j As Long
    Dim startDate As Date, endDate As Date, currentDate As Date
    
    ' Update this to your source worksheet name
    Set wsSource = ThisWorkbook.Worksheets("Sheet1")
    ' Create a new sheet for the expanded data
    Set wsOutput = ThisWorkbook.Worksheets.Add
    wsOutput.Name = "ExpandedDates"
    
    ' Copy header row to the output sheet
    wsSource.Rows(1).Copy wsOutput.Rows(1)
    ' Add a header for the expanded date column
    wsOutput.Cells(1, wsSource.UsedRange.Columns.Count + 1).Value = "ExpandedDate"
    
    ' Find the last row with data in column H
    lastRow = wsSource.Cells(wsSource.Rows.Count, "H").End(xlUp).Row
    
    ' Start writing data from row 2 on the output sheet
    j = 2
    
    For i = 2 To lastRow
        startDate = wsSource.Cells(i, "H").Value
        endDate = wsSource.Cells(i, "I").Value
        
        ' Loop through each date in the range
        currentDate = startDate
        Do While currentDate <= endDate
            ' Copy the original row's data to the output sheet
            wsSource.Rows(i).Copy wsOutput.Rows(j)
            ' Insert the current date from the range
            wsOutput.Cells(j, wsSource.UsedRange.Columns.Count + 1).Value = currentDate
            j = j + 1
            currentDate = currentDate + 1
        Loop
    Next i
    
    ' Format the expanded date column to match your date format
    wsOutput.Columns(wsSource.UsedRange.Columns.Count + 1).NumberFormat = "mm/dd/yyyy"
    
    MsgBox "Date ranges expanded successfully!", vbInformation
End Sub

To use this:

• Press Alt + F11 to open the VBA Editor.
• Right-click your workbook in the Project Explorer > Insert > Module.
• Paste the code above, update the Sheet1 reference to match your source sheet name.
• Press F5 to run the macro.

Quick note: For 16k rows, Power Query will be faster and less likely to freeze Excel compared to VBA, especially if many ranges are near the 365-day limit.

内容的提问来源于stack exchange，提问作者Clément Hurel