我有以下数据集
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)”有关,但不确定如何修复它。
答案1
您应该检查要求和的范围中是否有空白单元格。如果有,那么可以说它是一个单元格,因此不要将 BottomCell 设置为.End(xlDown)
。在这种情况下,它将与 是同一个单元格TopCell
。
我采用了略有不同的方法,但想法是一样的。它将从A1
A 列的最后一行开始:
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