excel - Counting upward in column until blank?

Question

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?

Answer 1

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