Excel VBA - 如何查找从活动单元格到空白单元格的空白单元格和总和

I have the following code to find the first blank cell and sum the data below it at the last blank cell.

我有以下代码来查找第一个空白单元格，并在最后一个空白单元格中对它下面的数据求和。

Dim r As Range
Dim lngRowStart As Long
If Range("A1").Formula <> "" Then
    lngRowStart = 1
Else
    lngRowStart = Range("A1").End(xlDown).Row
End If
    Set r = Cells(lngRowStart, 1).End(xlDown).Offset(1, 0)
If Left(r.Offset(-1, 0).Formula, 1) <> "=" Then
        r.FormulaR1C1 = "=Subtotal(9,R[-" & r.Row - lngRowStart & "]C:R[-1]C)"
End If

But this assumes that the data is in column A and for the first set of continuous data, how to modify it for any active cell to sum the above continuous data?

但这假设数据在A列中，对于第一组连续数据，如何修改任何活动单元以对上述连续数据求和？

For example:

例如：

2

4

3

Blank (SUM ABOVE=9)

1

3

2

Blank (SUM ABOVE=6)

1 个解决方案

#1

You can use the UDF below (explanation inside the code's comments):

您可以使用下面的UDF（代码注释中的解释）：

Function SumContinRange(CurCell As Range) As Double    
    Dim RngStart As Range, SumRng As Range        

    If CurCell <> "" Then
        ' find the first empty cell using the Find function
        Set RngStart = Columns(CurCell.Column).Find(what:="", After:=CurCell, LookIn:=xlValues)
    Else
        ' find the first empty cell using the Find function
        Set RngStart = Columns(CurCell.Column).Find(what:="", After:=CurCell, LookIn:=xlValues, SearchDirection:=xlPrevious)
    End If

    ' set the Sum Range
    Set SumRng = Range(RngStart.Offset(-1, 0), RngStart.Offset(-1, 0).End(xlUp))

    SumContinRange = WorksheetFunction.Sum(SumRng) ' return this value   
End Function

Then, test it by passing the ActiveCell using the Sub below:

然后，使用下面的Sub传递ActiveCell来测试它：

Sub TestFunc()

If ActiveCell.Value <> "" Then
    ActiveCell.End(xlDown).Offset(1) = SumContinRange(ActiveCell)
Else
    ActiveCell = SumContinRange(ActiveCell)
End If  

End Sub

#1