Excel月度汇总表新增空行A列填充当月递增日期需求

I’ve got you covered! Based on your setup—where your monthly summary inserts a blank row every 10 lines to separate daily data—here are two tailored VBA solutions to fill those blank rows in Column A with incrementing dates for the month.

Option 1: Insert Blank Rows + Fill Dates in One Go (Most Efficient) #

This approach integrates the date-filling logic directly into your existing summary generation code, so you don’t have to run a separate routine later. It assumes each daily report is in its own worksheet, and we’ll copy each day’s data, insert a blank row after it, and immediately populate the date.

Sub GenerateMonthlySummaryWithDates()
    Dim wsSummary As Worksheet
    Dim wsDaily As Worksheet
    Dim lastRow As Long
    Dim currentDate As Date
    
    ' Set your summary sheet name (update this to match your actual sheet)
    Set wsSummary = ThisWorkbook.Worksheets("Monthly Summary")
    
    ' Clear existing summary data (optional—remove if you want to append)
    wsSummary.Cells.Clear
    
    ' Start with the first day of the current month
    currentDate = DateSerial(Year(Date), Month(Date), 1)
    
    ' Loop through all worksheets except the summary sheet
    For Each wsDaily In ThisWorkbook.Worksheets
        If wsDaily.Name <> wsSummary.Name Then
            ' Copy daily report data to the summary sheet
            lastRow = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row + 1
            wsDaily.UsedRange.Copy wsSummary.Cells(lastRow, "A")
            
            ' Insert a blank row after the copied daily data
            wsSummary.Rows(lastRow + wsDaily.UsedRange.Rows.Count).Insert Shift:=xlDown
            
            ' Fill the date in Column A of the new blank row
            wsSummary.Cells(lastRow + wsDaily.UsedRange.Rows.Count, "A").Value = currentDate
            
            ' Move to the next day's date
            currentDate = currentDate + 1
        End If
    Next wsDaily
    
    ' Clean up: Remove the last extra blank row (if needed)
    lastRow = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row
    If wsSummary.Cells(lastRow, "A").Value = "" Then
        wsSummary.Rows(lastRow).Delete
    End If
    
    ' Optional: Format Column A as dates (adjust the format to your preference)
    wsSummary.Columns("A").NumberFormat = "mm/dd/yyyy"
    
    MsgBox "Monthly summary with dates is ready!", vbInformation
End Sub

Key Notes for Option 1: #

• Replace "Monthly Summary" with your actual summary sheet name.
• The code automatically skips the summary sheet when looping through daily reports.
• It handles daily reports of any length (not just 10 rows)—the blank row is inserted right after each day’s data, regardless of how many rows it has.

Option 2: Fill Dates in Existing Blank Rows #

If you already have a routine that inserts blank rows every 10 lines and just need to add the dates, use this standalone subroutine. It targets every 11th row (since 10 data rows + 1 blank row) and fills in incrementing dates starting from the first day of the month.

Sub FillDatesInExistingBlankRows()
    Dim wsSummary As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentDate As Date
    
    ' Set your summary sheet name
    Set wsSummary = ThisWorkbook.Worksheets("Monthly Summary")
    
    ' Start with the first day of the current month
    currentDate = DateSerial(Year(Date), Month(Date), 1)
    
    ' Find the last row with data in the summary sheet
    lastRow = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through every 11th row (adjust the starting number if your first blank row is different)
    For i = 11 To lastRow Step 11
        ' Only fill if the row is blank (avoids overwriting existing data)
        If wsSummary.Cells(i, "A").Value = "" Then
            wsSummary.Cells(i, "A").Value = currentDate
            currentDate = currentDate + 1
        End If
    Next i
    
    ' Optional: Format Column A as dates
    wsSummary.Columns("A").NumberFormat = "mm/dd/yyyy"
    
    MsgBox "Dates added to blank rows successfully!", vbInformation
End Sub

Key Notes for Option 2: #

• If your first blank row isn’t at row 11 (e.g., your summary has a header row), adjust the starting value in the For loop (e.g., start at 12 if row 1 is a header).
• The Step 11 ensures we jump from one blank row to the next (10 data rows + 1 blank row).

Quick Tips #

• Test the code on a copy of your workbook first to avoid accidental data loss.
• If you need to use a specific month (not the current one), replace DateSerial(Year(Date), Month(Date), 1) with a hardcoded date like #10/1/2024# for October 1, 2024.

内容的提问来源于stack exchange，提问作者Jb83