Summing rows above blank cell until previous blank cell

Question

Hello!

I am trying to build a macro, that finds a blank cell in a range in a single column and sums all cells between this blank cell and the previous blank cell.

I've searched the web a lot, and while others have asked this question, i do not find the answers to them particularly helpful in my situation, as i need this to work through 3500+ rows.

E.g:

4  
3  
4  
BLANK 1  
2  
5  
7  
1  
BLANK 2  
1  
4  
BLANK 3

In this case the cell called "BLANK 1" would be the sum of the 3 previous rows: 4+3+4=11
"Blank 2" would be 15 and so forth.

The range is "G8:G3561".

Edit

For the quick answer see Mr_Nitrogen's answer. It works beautifully! However, as I am new to VBA and coding in general I do not know how or why the code works.

I am very eager to obtain a better understanding of VBA, which is why I'm continuing this thread (if allowed to).
Furthermore, I would like to provide evidence for the commenters that I have indeed worked on this myself and that I prefer to build my own code.

It's important for me to understand why my code works or doesn't work, which is why i hope that you still want to help me develop my own code.

I finally found an approach that is logical to me. I know that this is not the simplest way to do it, but I would like to know if it could work.

I've written the following code.

Sub Sum_storage()
Dim rng As Range
Dim cell As Range
Dim cell2 As Range
Dim cell3 As Range

Range("G8").End(xlDown).Offset(1, 0).Select
Set cell = Selection
cell.Value = "temp" 'Finds the first blank cell in column G _
                     and creates a temporary value in order _
                     to find the second blank cell

Range("G8").End(xlDown).Offset(1, 0).Select
Set cell2 = Selection

cell.Offset(1, 0).Select
Set cell3 = Selection  'The range i need to sum can _
                        now be described as "cell3:cell2"

Set rng = Range(Range("cell3"), Range("cell2")) 'The code works until this point
cell2.Value = WorksheetFunction.Sum(rng)

The idea is to define the range i want to sum with multiple variables.
My problem is trying to refer to these variables (and setting them in an easier way than using .Offset).

Is it simply not possible to set a range (rng) based on two previously set ranges?

If this is possible the next step for me is creating some kind of loop that could make this work for all 3500+ rows.

Answer 1

This isnt that complicated to do with for loop, maybe something like

lastrow = Cells(Rows.Count, "G").End(xlUp).Row
firstrow = 8
TempTotal = 0
for x = firstrow to lastrow + 1
    If Cells(x, "G") <> "" Then
        TempTotal = TempTotal + Cells(x, "G")
    Else:   Cells(x, "G") = TempTotal
            Cells(x, "G").Interior.ColorIndex = 4
            TempTotal = 0
    End if
    Next x

Ive made an edit to make it a bit simpler

The Logic of the Code:

1. Define the last row with data in column "G"
2. Move down cell by cell until that row
3. If the cell has a value in it, add it to the temporary total.

4. If it is blank, inset the temporary total and reset the tempTotal to zero

   1. This is the first line, in which we select the very last last cell in column "G", use use End(xlup) on it to get to the last cell with data and use .row to get the row number of that cell

   2. Set up a For loop, which runs the code between "for" and "next x" lines repeatedly while incrementing the value of x from "firstrow" to "lastrow + 1" each time it repeats ( so if firsrow is 1 and lastrow is 100) then it will run the code 100 times with x = 1,2,3,4,5 etc.)

   3. this is the "if" statement, "<>" means does not equal, so we are saying if the cell on row x, col "G" is not equal to "" which is an empty string (or nothing) then we do the next line (add its value to tempTotal)
   4. if the "If" statement isnt true (if the cell is blank) then we do what is under the "Else" and make that cell equal to TempTotal, change its color to green (4 is a colorcode, they go between 1 and 50), and reset the temptotal to 0.