如何创建单元格区域且避免覆盖原有内容？

问题原因分析 #

你遇到的区域覆盖问题，大概率是两个原因导致：

1. 区域变量未按列重置：如果外层是按列循环（i为列索引），但rngDebit和rngCredit没有在处理每一列前重置为Nothing，会导致上一列的单元格区域被带入当前列的合并操作，最终求和区域包含错误的单元格。
2. Union方法的限制：Excel的Union对单个Range对象能包含的不连续区域数量有上限（最多255个），当超过这个数量时，新添加的区域会覆盖或丢失之前的部分区域。

解决方案 #

方案1：重置区域变量（基础修复） #

在处理每一列前，将rngDebit和rngCredit重置为Nothing，确保每列的区域计算独立：

' 外层列循环示例
For i = 1 To TargetColumnCount ' 替换为你的列数范围
    ' 关键：每列循环前重置区域变量
    Set rngDebit = Nothing
    Set rngCredit = Nothing

    For a = 1 To LastRow ' 行循环
        If .Cells(a, i).Value > 0 Then
            If rngDebit Is Nothing Then
                Set rngDebit = .Cells(a, i)
            Else
                Set rngDebit = Union(.Cells(a, i), rngDebit)
            End If
        Else
            If rngCredit Is Nothing Then
                Set rngCredit = .Cells(a, i)
            Else
                Set rngCredit = Union(.Cells(a, i), rngCredit)
            End If
        End If
    Next a

    ' 写入求和公式
    If Not rngDebit Is Nothing Then
        .Cells(LastRow + 2, i).Formula = "=Sum(" & rngDebit.Address & ")"
    Else
        .Cells(LastRow + 2, i).Value = 0
    End If
Next i

方案2：直接拼接地址字符串（规避Union限制） #

当需要合并大量不连续单元格时，直接拼接单元格地址比Union更可靠，不受不连续区域数量限制：

For i = 1 To TargetColumnCount ' 替换为你的列数范围
    Dim debitAddr As String, creditAddr As String
    debitAddr = ""
    creditAddr = ""

    For a = 1 To LastRow ' 行循环
        If .Cells(a, i).Value > 0 Then
            If debitAddr <> "" Then debitAddr = debitAddr & ","
            debitAddr = debitAddr & .Cells(a, i).Address
        Else
            If creditAddr <> "" Then creditAddr = creditAddr & ","
            creditAddr = creditAddr & .Cells(a, i).Address
        End If
    Next a

    ' 写入求和公式
    If debitAddr <> "" Then
        .Cells(LastRow + 2, i).Formula = "=Sum(" & debitAddr & ")"
    Else
        .Cells(LastRow + 2, i).Value = 0
    End If
Next i

方案3：使用SpecialCells筛选（高效替代） #

如果只是筛选列中数值大于0的单元格，用SpecialCells或自动筛选可以跳过循环，代码更简洁高效：

For i = 1 To TargetColumnCount ' 替换为你的列数范围
    On Error Resume Next ' 处理无符合条件单元格的情况
    ' 筛选当前列中数值大于0的单元格
    .Columns(i).AutoFilter Field:=1, Criteria1:=">0"
    Set rngDebit = .Columns(i).Range(.Cells(1, 1), .Cells(LastRow, 1)).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    ' 写入求和公式
    If Not rngDebit Is Nothing Then
        .Cells(LastRow + 2, i).Formula = "=Sum(" & rngDebit.Address & ")"
    Else
        .Cells(LastRow + 2, i).Value = 0
    End If

    .AutoFilterMode = False ' 关闭自动筛选
Next i

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