如何实现Excel宏动态填充至表格最后有效行？

核心问题是你得先找准表格数据的最后有效行，而不是从当前单元格往下盲目查找——毕竟新插入的M列只有M2有内容，Selection.End(xlDown)会直接跳到Excel的列尾（因为下面全是空的）。

1. 获取最后有效行号 #

找一个原本就有完整数据的列（比如M列的前一列L，毕竟M是新插的，L列肯定包含全量数据），用这段代码拿到最后一行的行号：

Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, "L").End(xlUp).Row

要是拿不准哪列有完整数据，也可以用UsedRange，但优先选确定有连续数据的列，准确性更高。

2. 替换AutoFill代码 #

把原来的

Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))

替换成：

Range("M2").AutoFill Destination:=Range("M2:M" & lastRow)

D列的AutoFill用同样逻辑，比如通过C列获取最后行号：

lastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("D2").AutoFill Destination:=Range("D2:D" & lastRow)

优化后的完整宏代码 #

（删掉了录制宏自带的冗余Select操作，运行更快且更稳定）

Sub Test6()
    Dim lastRow As Long
    
    ' 取消表格格式
    ActiveSheet.ListObjects("report_2023_2_21_143341").Unlist
    
    ' 插入M列并设置内容
    Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("M1").FormulaR1C1 = "Count"
    ' 获取L列最后有效行
    lastRow = ActiveSheet.Cells(Rows.Count, "L").End(xlUp).Row
    Range("M2").FormulaR1C1 = "=RC[-1]-1"
    Range("M2").AutoFill Destination:=Range("M2:M" & lastRow)
    
    ' 插入D列并设置内容
    Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").FormulaR1C1 = "Data"
    ' 通过C列获取最后行（若L列数据仍完整，也可复用上面的lastRow）
    lastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
    Range("D2").FormulaR1C1 = "=RC[-2]+RC[10]"
    Range("D2").AutoFill Destination:=Range("D2:D" & lastRow)
    
    ' 设置格式
    Range("D2:D" & lastRow).Font.Bold = True
    Range("D2:D" & lastRow).NumberFormat = "dd/mm/yyyy;@"
End Sub

关键注意点 #

• 选对有完整数据的列来获取lastRow是核心，确保填充范围精准匹配实际数据行数
• 移除不必要的Select操作，既能提升宏的运行速度，也能避免因选中区域变动导致的错误

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