在列中向上计数直到空白？ [英] 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 
  

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屋！