VBA：带隐藏行/筛选模式下高效写入变量值到多区域

问题根源 #

当操作的Range包含多个不连续子区域（比如示例中的A5:BG20001,A20005:BG30000），或是筛选后由可见单元格组成的非连续区域时，直接用数组读写会出现错位/重复值问题：

• Rng.Value读取时，Excel会将所有区域的单元格按行顺序拼成一个连续的二维数组
• 但写回时，Excel会把数组内容按「子区域为单位」重复填充，每个子区域的所有行都会被数组的第一行覆盖，而非按顺序匹配数组的对应行

高效解决方案（避免逐单元格循环） #

通过遍历Range的每个子区域（Area），将数组拆分为对应长度的片段分别写入，既保留数组操作的高效性，又解决非连续区域的赋值问题。

代码示例（多区域场景）

Dim Rng As Range, RngVar As Variant
Dim subRng As Range
Dim rowOffset As Long, subRowCount As Long
Dim i As Long

' 定义非连续Range
Set Rng = Range("A5:BG20001,A20005:BG30000")
' 读取所有数据到连续数组
RngVar = Rng.Value

' --------------------------
' 这里是你的行更新逻辑，示例：给每行第一列数值加1
For i = LBound(RngVar) To UBound(RngVar)
    If IsNumeric(RngVar(i, 1)) Then
        RngVar(i, 1) = RngVar(i, 1) + 1
    End If
Next i
' --------------------------

rowOffset = LBound(RngVar) - 1 ' 数组起始行偏移量，用于拆分片段

' 遍历每个子区域，写入对应数组片段
For Each subRng In Rng.Areas
    subRowCount = subRng.Rows.Count
    ' 用Application.Index快速截取数组的指定行范围，写入子区域
    subRng.Value = Application.Index(RngVar, _
        Evaluate("ROW(" & (rowOffset + 1) & ":" & (rowOffset + subRowCount) & ")"), _
        0)
    rowOffset = rowOffset + subRowCount ' 更新偏移量，处理下一个子区域
Next subRng

筛选场景适配

如果是筛选后的可见区域，只需先获取可见单元格范围，再复用上述逻辑：

' 先获取筛选后的可见区域
Set Rng = Range("A5:BG30000").SpecialCells(xlCellTypeVisible)
' 后续数组读取、更新、拆分写入逻辑和上面完全一致

关键说明 #

• Application.Index是VBA中快速拆分二维数组行的高效方法，比逐行循环赋值快一个数量级
• 遍历Rng.Areas可以精准匹配每个非连续子区域，确保数组片段和区域行数完全对应，避免错位

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