Excel 365 VBA中Range.FillDown无法填充指定单元格的问题

问题根源 #

你遇到的Range.FillDown失效问题，核心原因是：调用该方法的Range仅包含新插入的空行单元格，没有包含上方带有公式的源单元格。FillDown的作用是将范围内最顶部的单元格内容填充到下方单元格，若范围里只有空单元格，自然无法生成内容。

而前两个Cells.FillDown能生效，是Excel的隐性行为——对单个单元格调用该方法时，它会自动向上扩展范围到最近的非空单元格，但这种行为依赖Excel默认规则，并不稳定。

修正方案 #

需要明确指定包含**源行（新插入行的上一行）和目标行（新插入行）**的范围，同时为所有Cells显式指定所属工作表，避免引用歧义。

修正后的代码 #

Sub insert_sheet_row()
    'Inserts a row into the sheet above the currently selected cell.  Ranges with formulas are specified in
    'this sub and are updated via filldown.

    Dim current_row As Integer
    Dim fcst_total_col_1 As Integer
    Dim fcst_total_col_2 As Integer
    Dim fcst_total_col_3 As Integer

    '''Constants included for upload to Stack Overflow
    Dim FCST_START_COL As Integer
    Dim FCST_END_COL As Integer
    Dim TM_TBL_HEADER_ROW As Integer

    FCST_START_COL = 14
    FCST_END_COL = 88
    TM_TBL_HEADER_ROW = 20
    '''End Constants '''


    'Assign the current row from the active cell.
    current_row = Selection.Row

    'Pattern to select ranges that contain formulas
    fcst_total_col_1 = FCST_START_COL + 4
    fcst_total_col_2 = fcst_total_col_1 + 5
    fcst_total_col_3 = fcst_total_col_2 + 5

    'Only run if current_row is greater than table header -- currently set to integer 20.
    If current_row > TM_TBL_HEADER_ROW Then

        'Insert new row
        Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        'Fill formulas for appropriate ranges.

        ' 修正：明确包含源行和目标行的范围，同时显式指定工作表
        ActiveSheet.Range(ActiveSheet.Cells(current_row - 1, fcst_total_col_1), ActiveSheet.Cells(current_row, fcst_total_col_1)).FillDown
        ActiveSheet.Range(ActiveSheet.Cells(current_row - 1, fcst_total_col_2), ActiveSheet.Cells(current_row, fcst_total_col_2)).FillDown

        ' 修正：同样扩展范围到源行，且为Cells指定工作表
        ActiveSheet.Range(ActiveSheet.Cells(current_row - 1, fcst_total_col_3), ActiveSheet.Cells(current_row, FCST_END_COL)).FillDown

        'Printing range for debugging purposes
        Debug.Print (current_row & " " & fcst_total_col_3 & " to " & current_row & " " & FCST_END_COL)
    End If

End Sub

关键修改点 #

1. 范围扩展：将原来仅指向新行的范围，修改为包含current_row-1（插入前的选中行，现在是新行的上一行，带有公式）和current_row（新插入行）的连续范围，确保FillDown有可复制的源内容。
2. 工作表显式引用：给所有Cells加上ActiveSheet.前缀，避免在多个工作表切换时，Cells默认引用当前活动工作表导致的错误。

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