Sub CountUp()
Dim TotalRows As Long
Dim TotalCols As Long
Dim Col As Long
Dim i As Long
Dim n As Long
Rows(2).Insert Shift:=xlDown
TotalRows = ActiveSheet.UsedRange.Rows.Count
TotalCols = ActiveSheet.UsedRange.Columns.Count
'Assumes Data you want to sum is in the first column
Col = 1
Cells(TotalRows, Col).Select
For i = TotalRows To 1 Step -1
If Cells(i, Col).Value <> "" Then
n = n + 1
Else
Cells(i, Col).Formula = "=SUM(" & Cells(i + 1, Col).Address(False, False) & ":" & Cells(i + n, Col).Address(False, False) & ")"
n = 0
End If
Next
End Sub
1
假设列F为列,并且它具有数字 . 请尝试以下代码 .
Sub sumAndcount()
Dim recCount As Long
Dim recSum As Long
Range("A65000").Value = "=COUNT(F:F)"
recCount = Range("A65000").Value
Range("A65000").Value = "=SUM(F:F)"
recCount = Range("A65000").Value
End Sub
2 回答
我不是100%肯定你在问什么 . 您说“总和数字”,但不指定您要求的数字是计算的行数,还是要总和找到的单元格的值 .
-编辑-
尝试一下:这将从底行开始并向上计数直到找到一个空白单元格一旦找到一个空白单元格,它将对最后一个空白单元格和当前空白单元格之间的单元格求和 .
-Edit2-
在列 Headers 下的行中添加了插入,因此第一行也会被求和 .
假设列F为列,并且它具有数字 . 请尝试以下代码 .