Excel VBA：使用Range.AutoFill实现首空列公式批量填充

需求说明 #

在名为register的工作表中，找到第一空列的第2行输入指定公式，再将该公式向下填充至数据区域的所有行。

原代码问题修正与完善 #

原代码存在变量未声明、空列定位错误、填充范围未明确的问题，以下是修正后的完整代码：

Sub SetNextEmptyFormula(strFormula As Variant)
  Dim ws As Worksheet
  Dim lCol As Long, lRow As Long
  Dim sourceRange As Range, fillRange As Range ' 补充声明Range类型变量

  Set ws = Workbooks("myworkbook.xlsm").Worksheets("register")

  ' 获取首行最后一个有数据的列号
  lCol = ws.Range("A1").End(xlToRight).Column
  ' 获取A列最后一个有数据的行号（对应数据区域最后一行）
  lRow = ws.Range("A1").End(xlDown).Row
  
  ' 在第一空列的第2行输入公式
  ws.Cells(2, lCol + 1).Formula = strFormula ' 用Formula属性直接赋值，避免字符串转换出错

  ' 设置填充的源区域（即输入公式的单元格）
  Set sourceRange = ws.Cells(2, lCol + 1)
  ' 设置填充的目标区域（从源单元格延伸到该列数据最后一行）
  Set fillRange = ws.Range(sourceRange, ws.Cells(lRow, lCol + 1))
  
  ' 执行自动填充
  sourceRange.AutoFill Destination:=fillRange
End Sub

关键修改说明 #

• 空列定位：用lCol + 1定位第一空列，因为ws.Range("A1").End(xlToRight).Column获取的是首行最后一个非空列的编号，加1即为第一个空列。
• 公式赋值优化：改用Formula属性直接赋值公式，避免CStr转换可能导致的公式格式异常。
• 填充范围设置：
  • sourceRange指定为输入公式的单元格（第2行、第一空列）
  • fillRange指定为从源单元格到该列对应数据最后一行的整个区域，确保公式覆盖所有数据行。
• 规范变量声明：补充sourceRange和fillRange的类型声明，符合VBA代码编写规范。

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