Excel VBA如何实现每日新增数据的动态Consolidate合并计算

问题背景 #

需要将datafile.xlsx内的源数据通过Consolidate命令合并到同工作簿第2个工作表的A1起始位置，初始写死A1:C7固定范围的代码无法适配每日新增行的动态数据集，需要实现类似Range.CurrentRegion的自动识别全量有效数据范围的效果。
初始固定范围的代码如下：

Dim iwb As Workbook: Set iwb = Workbooks.Open("datafile.xlsx")
Dim insp As Worksheet: Set insp = iwb.Sheets(1)
Dim tot As Worksheet: Set tot = iwb.Sheets(2)

tot.Range("A1").consolidate _
    Sources:=Array("Inspector Data!R1C1:R7C3"), _
    Function:=xlSum, _
    TopRow:=True, _
    LeftColumn:=True

实现方案 #

Consolidate方法的Sources参数仅接受R1C1格式的地址字符串，不支持直接传入Range对象，因此只需要先动态获取源表的有效数据范围，再转换为符合要求的R1C1地址传入即可。
修正后可自动适配数据新增的代码如下：

Dim iwb As Workbook: Set iwb = Workbooks.Open("datafile.xlsx")
Dim insp As Worksheet: Set insp = iwb.Sheets(1)
Dim tot As Worksheet: Set tot = iwb.Sheets(2)

' 动态获取A1起始的连续有效数据块
Dim dynamicSourceRng As Range
Set dynamicSourceRng = insp.Range("A1").CurrentRegion

' 转换为Consolidate要求的带工作表名的R1C1格式地址
Dim sourceAddress As String
sourceAddress = insp.Name & "!" & dynamicSourceRng.Address(ReferenceStyle:=xlR1C1)

' 执行合并
tot.Range("A1").Consolidate _
    Sources:=Array(sourceAddress), _
    Function:=xlSum, _
    TopRow:=True, _
    LeftColumn:=True

注意事项 #

• CurrentRegion识别的是和A1单元格连续的非空数据块，和手动选中A1后按Ctrl+A的选中逻辑完全一致，只要新增行和原有数据区域连续（没有整行空行隔断），就会被自动纳入合并范围
• 必须将范围地址转换为R1C1引用样式，Consolidate方法无法识别A1样式的地址字符串，会触发运行错误
• 如果数据区域存在固定空行隔断，可以替换范围获取逻辑：通过定位A列最后一个非空行、第1行最后一个非空列的方式构造数据范围，适配非连续场景。

内容的提问来源于stack exchange，提问作者Stefano Di Loreto