Excel xlDown 选择两个数据块

Question

我有以下数据集

100  
100  


200  
200  


300  


400

我正在使用此代码

    Dim rngToSum As Range
    Dim TopCell As Range
    Dim BottomCell As Range

    Set rngToSum = Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)
    Set TopCell = rngToSum.Cells(1, 1)

    If TopCell.Value = vbNullString Then
        Set TopCell = TopCell.End(xlDown)
    End If

    Do
        Set BottomCell = TopCell.End(xlDown)
        BottomCell.Offset(1, 0).Formula = "=sum(" & Range(TopCell, BottomCell).Address & ")"
        Set TopCell = BottomCell.Offset(1, 0).End(xlDown)
    Loop Until Intersect(rngToSum, TopCell) Is Nothing

End Sub

它对有 2 行的数据（例如 100 和 100）进行求和，但不是在 300 之后返回 300，也不是在 400 之后返回 400，而是在 400 之后返回 700，因为它选择了它们两个进行求和。我知道这与从底线开始的第三行“End(xlDown)”有关，但不确定如何修复它。

Answer 1

您应该检查要求和的范围中是否有空白单元格。如果有，那么可以说它是一个单元格，因此不要将 BottomCell 设置为.End(xlDown)。在这种情况下，它将与 是同一个单元格TopCell。

我采用了略有不同的方法，但想法是一样的。它将从A1A 列的最后一行开始：

Sub sum_blocks()
Dim singleRow As Boolean
Dim rng As Range
Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp))

Dim i As Long
Dim rngToSum As Range
Dim topCell As Range, bottomCell As Range

For i = 1 To rng.Rows.Count
    singleRow = False
    Set topCell = Cells(i, 1)
    Set bottomCell = topCell.End(xlDown)
    Range(topCell, bottomCell).Select
    If WorksheetFunction.CountA(Range(topCell, bottomCell)) <> Range(topCell, bottomCell).Cells.Count Then
        singleRow = True
        Set bottomCell = topCell
    End If
    Set rngToSum = Range(topCell, bottomCell)
    rngToSum.Select
    bottomCell.Offset(1, 0).Formula = "=SUM(" & rngToSum.Address & ")"
    bottomCell.Offset(1, 0).Font.Bold = True
    bottomCell.Offset(1, 0).Font.Color = vbRed
    If singleRow Then
        i = bottomCell.Row + 1
    Else
        i = bottomCell.Offset(1, 0).End(xlDown).Row - 1 ' bottomCell.Row + 1
    End If
Next i

End Sub