Excel VBA中Range.AutoFill方法报错'1004'问题求助

问题场景 #

我需要给Excel表格的新行填充G列至L列的公式，编写了以下VBA宏，但执行ws.Range(sourceRangeAddress).AutoFill Destination:=ws.Range(destinationRangeAddress), Type:=xlFillDefault时，弹出“Range类的AutoFill方法失败 '1004'”错误。改用Copy方法可正常运行，但想解决AutoFill的报错问题。

原代码 #

AutofillNewRows 过程

Private Sub AutofillNewRows(ByRef modifiedRows As Collection)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Database")
    Set modifiedRows = New Collection
    
    Dim lastRow As Long
    Dim row As Long
    Dim col As Long
    Dim startColumn As Long
    Dim endColumn As Long
    Dim isRowEmpty As Boolean
    
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    
    startColumn = 7 ' Column G
    endColumn = 12 ' Column L
    
    ' Find last used row in column G
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).row

    ' Loop through each row from bottom to the first row of data
    For row = lastRow To 4 Step -1 ' Assuming row 4 is headers
        isRowEmpty = False
        For col = startColumn To endColumn ' Columns G to L
            If IsEmpty(ws.Cells(row, col)) Then
                isRowEmpty = True
                Exit For
            End If
        Next col
        
        If isRowEmpty Then
            Call AutoFillWithCopyMethod(ws, row)
            modifiedRows.Add row
            ' HN Handling
            ws.Cells(row, "HN").Formula = "=IF(ISNA(MATCH($A" & row & ",$A$3:$A" & row - 1 & ",0)),1,0)"
        End If
    Next row
End Sub

AutoFillWithCopyMethod 过程

Sub AutoFillWithCopyMethod(ws As Worksheet, row As Long)
    Dim sourceRangeAddress As String
    Dim destinationRangeAddress As String
    
    sourceRangeAddress = "G" & (row - 1) & ":L" & (row - 1)
    destinationRangeAddress = "G" & row & ":L" & row
    
    ws.Range(sourceRangeAddress).AutoFill Destination:=ws.Range(destinationRangeAddress), Type:=xlFillDefault
    ' If you want to only copy formulas without formatting or data validation
    ' ws.Range(destinationRangeAddress).Formula = ws.Range(sourceRangeAddress).Formula
End Sub

错误原因 #

AutoFill方法的核心要求是：源区域必须是目标区域的一部分，且两者需构成连续的单元格范围。原代码中，源区域是上一行的G-L列，目标区域是当前行的G-L列，两者是分开的不连续范围，不符合AutoFill的调用规则，因此触发1004错误。

解决办法 #

修改AutoFillWithCopyMethod过程，先构建包含源区域和目标区域的连续范围，再指定源区域作为填充基准：

Sub AutoFillWithCopyMethod(ws As Worksheet, row As Long)
    Dim sourceRange As Range
    Dim fillRange As Range
    
    ' 源区域：上一行的G-L列
    Set sourceRange = ws.Range("G" & (row - 1) & ":L" & (row - 1))
    ' 填充范围：源区域 + 当前行的G-L列（连续范围）
    Set fillRange = ws.Range("G" & (row - 1) & ":L" & row)
    
    ' 以源区域为基准，填充整个连续范围
    sourceRange.AutoFill Destination:=fillRange, Type:=xlFillDefault
End Sub

说明 #

• 这里先定义了包含两行的连续范围fillRange，源区域sourceRange是它的上半部分，符合AutoFill的参数要求。
• 如果只需要复制公式而不需要格式/数据验证，也可以保留你原来注释的Copy公式方案：

  ws.Range("G" & row & ":L" & row).Formula = ws.Range("G" & (row - 1) & ":L" & (row - 1)).Formula
  

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