Sum variable blocks of cells

[CookieMonster76]

Hi

I have the following

A1 100
A2 100
A3
A4 100
A5 100
A6 100
A7
A8 100
A9 100
A10

I cells A3, A7 and A10 (currently empty) I want to sum the preceding cells. However, they are variable in length each time (i.e. Next time it may be that A4 will be summing A1 to A3).

In short I need the blank cells to sum those directly preceding them (but I don't want A7 to sum A1 to A6 once A3 has been populated, just A4 to A7).

I hope this makes sense and thanks for any help.

Paul

 

[Fluff]

Is col A hard values, or formulae?

 

[CookieMonster76]

Hi

They are hard values - But will change each time what i am trying to do is created (i.e. they are hard coded but will be a different value.

 

[Fluff]

In that case how about

Sub CookieMonster()
   Dim Rng As Range
   
   For Each Rng In Range("A:A").SpecialCells(xlConstants).Areas
      Rng.Offset(Rng.Count)(1).Formula = "=sum(" & Rng.Address & ")"
   Next Rng
End Sub

 

[CookieMonster76]

In that case how about

Sub CookieMonster()
   Dim Rng As Range
  
   For Each Rng In Range("A:A").SpecialCells(xlConstants).Areas
      Rng.Offset(Rng.Count)(1).Formula = "=sum(" & Rng.Address & ")"
   Next Rng
End Sub

Legend in your own lifetime

Thank you

 

[Fluff]

You're welcome & thanks for the feedback.