如何在Excel VBA中为现有数组添加一行零并调整大小？

你从单元格区域生成的是7行15列的二维数组（VBA中从Range赋值的数组默认下标从1开始），要插入一行零到开头或末尾，核心思路是新建一个行数+1的数组，再把原数据和零行复制进去，具体实现如下：

方法1：在数组开头插入零行 #

Dim tempIn As Variant
Dim tempOut As Variant
Dim i As Long, j As Long

' 你的原有数组赋值代码
tempIn = wsData.Range("Z" & lastRowData - 6 & ":AN" & lastRowData).Value

' 新建8行15列的目标数组
ReDim tempOut(1 To 8, 1 To 15)

' 把第一行全部设为0
For j = 1 To 15
    tempOut(1, j) = 0
Next j

' 将原数组的7行数据复制到新数组的第2-8行
For i = 1 To 7
    For j = 1 To 15
        tempOut(i + 1, j) = tempIn(i, j)
    Next j
Next i

' 此时tempOut就是插入了开头零行的目标数组

方法2：在数组末尾插入零行 #

逻辑和开头插入类似，先复制原数据，再填充最后一行零：

Dim tempIn As Variant
Dim tempOut As Variant
Dim i As Long, j As Long

' 你的原有数组赋值代码
tempIn = wsData.Range("Z" & lastRowData - 6 & ":AN" & lastRowData).Value

' 新建8行15列的目标数组
ReDim tempOut(1 To 8, 1 To 15)

' 将原数组的7行数据复制到新数组的第1-7行
For i = 1 To 7
    For j = 1 To 15
        tempOut(i, j) = tempIn(i, j)
    Next j
Next i

' 把最后一行全部设为0
For j = 1 To 15
    tempOut(8, j) = 0
Next j

' 此时tempOut就是插入了末尾零行的目标数组

通用版代码（适配任意行列数） #

如果不想硬编码行数和列数，可以用UBound获取原数组的维度，让代码更灵活：

Dim tempIn As Variant
Dim tempOut As Variant
Dim rowCount As Long, colCount As Long
Dim i As Long, j As Long

tempIn = wsData.Range("Z" & lastRowData - 6 & ":AN" & lastRowData).Value
rowCount = UBound(tempIn, 1) ' 获取原数组行数
colCount = UBound(tempIn, 2) ' 获取原数组列数

' 新建行数+1的目标数组
ReDim tempOut(1 To rowCount + 1, 1 To colCount)

' --- 插入开头零行的逻辑 ---
For j = 1 To colCount
    tempOut(1, j) = 0
Next j
For i = 1 To rowCount
    For j = 1 To colCount
        tempOut(i + 1, j) = tempIn(i, j)
    Next j
Next i

' --- 如果要插入末尾，替换成这段逻辑 ---
' For i = 1 To rowCount
'     For j = 1 To colCount
'         tempOut(i, j) = tempIn(i, j)
'     Next j
' Next i
' For j = 1 To colCount
'     tempOut(rowCount + 1, j) = 0
' Next j

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