如何在Excel VBA中将公式向下填充至列的最后有效行

问题根源与修复方案 #

1. 原始D列填充溢出的问题 #

End(xlDown)找最后一行的逻辑有缺陷：如果C2下方存在空单元格，或者C列只有C2有值，End(xlDown)会直接跳到工作表最后一行（1048576），导致填充溢出。
正确做法是从C列最后一行向上倒推找C列的有效数据最后一行，逻辑更稳定。

2. AK列仅填充前两行的问题 #

两个错误导致：

• 混用了普通公式写法和FormulaR1C1属性：=IF(ISBLANK(C3),...)是A1引用格式的公式，不能赋值给FormulaR1C1属性，要么换用Formula属性，要么改成R1C1格式的公式
• 全局找最后一行的逻辑不稳定：用Cells.Find找全表最后一行如果遇到其他列比C列长的情况，会填充多余的行，应该限定找C列的最后一行

修正后的通用代码（兼容任意目标列） #

Sub FillMonthClosed(ByVal targetCol As String)
    Dim Output_Sh As Worksheet
    Dim lastRow As Long
    Dim fillRng As Range
    
    Set Output_Sh = ThisWorkbook.Sheets("Output")
    
    ' 获取C列最后一个有数据的行号
    lastRow = Output_Sh.Cells(Output_Sh.Rows.Count, "C").End(xlUp).Row
    
    ' 目标列小于2行有效数据直接退出
    If lastRow < 2 Then Exit Sub
    
    ' 插入目标列（如果不需要插入可以注释掉下一行）
    Output_Sh.Columns(targetCol & ":" & targetCol).Insert
    ' 写表头
    Output_Sh.Range(targetCol & "1").Value = "Month Closed"
    ' 批量填充公式，用A1引用格式不需要逐行调整行号，Excel会自动相对引用
    Set fillRng = Output_Sh.Range(targetCol & "2:" & targetCol & lastRow)
    fillRng.Formula = "=IF(ISBLANK(C2),""Still open"",TEXT(C2,""mmm""))"
End Sub

调用方式 #

• 填充D列就调用：Call FillMonthClosed("D")
• 填充AK列就调用：Call FillMonthClosed("AK")

额外说明 #

之前AK列代码的冗余问题：手动给AK2赋值公式、再给AK3及以后赋值完全没有必要，批量赋值的时候Excel会自动处理相对引用，C2会自动对应每行的C列单元格，不需要手动修改为C3、C4。

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