在列中向上计数直到空白?(Counting upward in column until blank?)

Office IT屋
问 题

I have a column, which will contain a few rows of data, and then a blank cell.

I need a way to count the rows upwards in a column until a blank cell and sum the number using VBA. Any ideas?

解决方案

I'm not 100% sure what you are asking. You say "sum the number" but do not specify if the number you want to sum is the number of rows counted or if you want to sum the value of the cells found.

-Edit-

Give this a try: This will start at the bottom row and count upward until it finds a blank cell Once a blank cell is found it will sum the cells between the last blank cell and the current blank cell.

-Edit2-

Added insert to the row under column headers so the first row also gets summed.

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

本文地址:IT屋 » Counting upward in column until blank?

问 题
我有一列,它将包含几行数据,然后是一个空白单元格。



我需要一种方法来在列中向上计数列,直到空白单元格,并使用 VBA 。任何想法?


解决方案

我不是100%肯定你在问什么。你说“和数”,但是不要指定你想要的数字是否是计算的行数,或者是否想要求出所找到的单元格的值。



-Edit -



尝试一下:
这将从底部开始,向上计数,直到找到一个空白单元格
一次找到一个空白单元格,它会将最后一个空白单元格和当前空白单元格之间的单元格相加。



-Edit2 -



将列插入列标题下的行,以便第一行也被求和。



  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
'假设要求的数据位于第一列
Col = 1

单元格(TotalRows,Col)。选择
For i = TotalRows到1 Step -1
如果Cells(i,Col).Value<> “”然后
n = n + 1
Else
单元格(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

本文地址:IT屋 » 在列中向上计数直到空白?