Sum variable blocks of cells

CookieMonster76

Board Regular
Joined
Apr 30, 2015
Messages
175
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
Is col A hard values, or formulae?
 

CookieMonster76

Board Regular
Joined
Apr 30, 2015
Messages
175
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
In that case how about
VBA Code:
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

Board Regular
Joined
Apr 30, 2015
Messages
175
In that case how about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,294
Messages
5,571,383
Members
412,385
Latest member
OChambo94
Top