如何清除16000行文件中VBA错误1004及实现日期批量更新

Hey there! Let's tackle your Excel VBA problem step by step. First, we'll fix the code to generate the date rows you need, then we'll troubleshoot that Error 1004 you're encountering.

Your goal is to populate column B with every date between the start (column H) and end (column I) dates from each row, with one date per row. Here's a robust, beginner-friendly code that handles edge cases and runs efficiently:

Sub GenerateDates()
    Dim ws As Worksheet
    Dim lastOriginalRow As Long
    Dim originalRow As Long
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    Dim outputRow As Long
    
    ' Set your target worksheet (replace "DataSheet" with your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("DataSheet")
    
    ' Find the last row with data in your original table (using column H as reference)
    lastOriginalRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
    
    ' Start outputting dates from row 2 (assuming row 1 is your header row)
    outputRow = 2
    
    ' Turn off screen updates to speed up the macro (critical for large datasets)
    Application.ScreenUpdating = False
    
    ' Loop through each original data row
    For originalRow = 2 To lastOriginalRow
        startDate = ws.Cells(originalRow, "H").Value
        endDate = ws.Cells(originalRow, "I").Value
        
        ' Validate the date range before proceeding
        If IsDate(startDate) And IsDate(endDate) And startDate <= endDate Then
            currentDate = startDate
            
            ' Populate each date in column B, one per row
            Do While currentDate <= endDate
                ws.Cells(outputRow, "B").Value = currentDate
                outputRow = outputRow + 1
                currentDate = currentDate + 1
            Loop
        Else
            ' Optional: Mark rows with invalid date ranges for review
            ws.Cells(outputRow, "B").Value = "⚠️ Invalid Date Range"
            outputRow = outputRow + 1
        End If
    Next originalRow
    
    ' Turn screen updates back on
    Application.ScreenUpdating = True
    MsgBox "Date generation finished!", vbInformation
End Sub

Key improvements in this code:

• Explicit worksheet reference (avoids relying on ActiveSheet, which causes errors)
• Screen updating disabled to handle large datasets faster
• Date validation to skip rows with invalid/backwards date ranges
• Clear variable names for easier debugging

Error 1004 is a common VBA issue with a few straightforward fixes. Here are the most likely causes and solutions for your scenario:

• Invalid worksheet/range references
  If your code uses ActiveSheet or refers to a sheet that doesn't exist, Excel throws this error. Always explicitly name your worksheet like we did in the code above (e.g., ThisWorkbook.Worksheets("DataSheet")).

• Protected worksheet
  If your sheet is password-protected, you can't modify cells via VBA without unlocking it first. Add these lines to your code (replace "YourPassword" with your actual password):

  ' Unlock the sheet before modifying cells
  ws.Unprotect Password:="YourPassword"
  
  ' ... your date generation code ...
  
  ' Re-protect the sheet when done
  ws.Protect Password:="YourPassword"
  

• Exceeding Excel's row limit
  Excel has a maximum of 1,048,576 rows. If generating all your dates would exceed this, you'll get Error 1004. Add this check inside the Do While loop to catch it early:

  If outputRow > ws.Rows.Count Then
      MsgBox "Hit Excel's maximum row limit! Stopping early.", vbExclamation
      Exit Sub
  End If
  

• Merged cells in column B
  Merged cells break VBA's ability to write to individual rows. Unmerge any cells in column B before running the macro.

• Invalid date values in columns H/I
  If cells in H or I contain non-date data (like text), VBA can't process them. The code above includes IsDate checks to skip these rows, preventing the error.

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