Excel VBA向新增表行写入短数组时如何避免覆盖末尾自动公式

场景说明 #

在Excel中操作结构化表（List Object）时，新增表行后需要将数组数据写入新行，但待写入数组长度小于单行总单元格数：

• 结构化表单行总单元格数：12
• 待写入数组长度：9

新行末尾3个单元格配置了带@结构化引用的公式，正常新增行时会自动从上一行复制填充，但当前测试的写入方式会覆盖这3个单元格，触发#N/A错误。

待写入的数组中包含公式类型的数据，示例构造逻辑如下：

tempArray(j)=Array("=HYPERLINK([@Location]," & Chr(34) & files.ListColumns("Name").DataBodyRange(i) & Chr(34) & ")",...,...,...,...)

约束说明：如果是不带@结构化引用的静态公式，可以不使用ListRows.Add方法实现写入（这是此前的实现方案），但当前场景必须保留结构化引用公式的自动关联能力，因此不能弃用ListRows.Add的新增行逻辑。

已测试的无效方案 #

目前已经尝试过多种范围选择的写入方式，均未达到预期，代码如下：

With data.ListRows.Add

    'Set newRng = .Range.Range(.Range.Cells(1, 1), .Range.Cells(1, 9))

    ' 尝试仅选中前9列写入，仍会覆盖末尾公式
    .Range.Range(.Range.Cells(1, 1), .Range.Cells(1, 9)).Value2 = tempArray(i)
    ' 直接写入整行，必然覆盖末尾公式
    .Range.Value2 = tempArray(i)
     
    ' 直接通过表区域定位行写入，同样破坏自动填充
    'data.Range(data.Cells(lr + 1, 1), data.Cells(lr + 1, UBound(tempArray(i)) + 1)).Value = tempArray(i)
    
    lr = lr + 1
    
End With

核心诉求 #

目前查到的同类问题解决方案大多采用逐单元格循环写入的方式，性能较差，需要实现一次性批量写入数组数据，不执行逐单元格操作，同时不破坏行尾3个单元格的自动结构化引用公式。

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