Sum after every blank cell

  • Thread starter Thread starter goldkarat24
  • Start date Start date
G

goldkarat24

Hello,

I love this website!! I have a problem that I am going nuts over. In
column A I have several numbers from A1 to A100
In that column there may be random blank spaces like A10 is blank, A15
is blank then A40 is blank.I would like a formula in column B that
sums A1to A9 then sums A11 to A14. Inotherwords, is there a formula
that will give me a sum after there is a blank cell?
Thanks
 
Hello,

I love this website!! I have a problem that I am going nuts over. In
column A I have several numbers from A1 to A100
In that column there may be random blank spaces like A10 is blank, A15
is blank then A40 is blank.I would like a formula in column B that
sums A1to A9 then sums A11 to A14. Inotherwords, is there a formula
that will give me a sum after there is a blank cell?
Thanks
Click to expand...

Hi

Try this macro.

Sub AddSumIfBlank()
Range("B1:B101").ClearContents
tCell = "A1"
tCell1 = "A1"
For c = 0 To 100
If Range(tCell).Offset(c, 0) = "" Then
tCell2 = Range(tCell).Offset(c, 0).Address
Range(tCell2).Offset(0, 1).Formula = "=SUM(" & tCell1 & ":" &
tCell2 & ")"
tCell1 = tCell2
End If
Next
End Sub


Regards,

Per
 
The macro works PERFECT. Can this only be achieved by macro or is
there a formula that can create the same results?
 
The macro works PERFECT. Can this only be achieved by macro or is
there a formula that can create the same results?
Click to expand...

Sorry, but this can only be achieved by macro:-(

If you want the macro to run every time a number is entered or deleted
from the target range, try to put this in the macro sheet related to
the sheet containing the data.

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Range("A1:A100"), Target)
If Not isect Is Nothing Then

Range("B1:B101").ClearContents
tCell = "A1"
tCell1 = "A1"
For c = 0 To 100
If Range(tCell).Offset(c, 0) = "" Then
tCell2 = Range(tCell).Offset(c, 0).Address
Range(tCell2).Offset(0, 1).Formula = "=SUM(" & tCell1 &
":" & tCell2 & ")"
tCell1 = tCell2
End If
Next
End If
End Sub

Regards,

Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filling Blanks only with ABOVE nonBlanks 4
Remove specific cells in a column. 2
Excel formula, sum the value cell and if empty cell left blank 0
Macro to insert sum calculation at every second blank cell for data above in column F 1
Bug in Excel? 2
Autofill cells in 1 column :code ?? 2
Move data from first 7 rows from one column A into multiple Columnsthen the next 7 rows 2
Re: How to calculate the number of non-zero cells in range? 5

Back
Top