Sum above to blank cell - dynamic range

[jasonfish11]

Hi,
I'm building a macro and trying to add sums in the total sections. I would like to sum everything above the formula until it reaches the 2nd blank cell (stopping at the header).

I'm struggling a bit with the logic.

Example I want to sum O9-O13 (notice O13 is a blank cell, hence the desire to go to 2nd blank) and put that total in O14. I also want to sum O17:O19 and have that value in O20, etc etc until I have no more data sub-sections.

The rows data in these subsections will be dynamic based on the reports I'm getting.

Any help with the logic would be greatly appreciated.

Thank you in advance,
Jason

 

[jasonfish11]

Sorry I made a very quick change meaning I don't have to go to the 2nd blank, just the 1st blank (or header). But didn't know how to delete & repost my original post.

 

[Fluff]

Is the header row always row 8 & are the cells with Total in col N?

 

[jasonfish11]

Is the header row always row 8 & are the cells with Total in col N?

Yes header will always be row 8.

The word "Total" is in column N, but the actual sum of the sub sections will be in column O and P. The columns are not dynamic, nor is the header. But everything below would be.

 

[Fluff]

Thanks for that, I forgot to ask are the values in col O hard values or formulae?

 

[jasonfish11]

Thanks for that, I forgot to ask are the values in col O hard values or formulae?

formulas

 

[Fluff]

In that case will the formula also be in the "blank" cells?

 

[jasonfish11]

In that case will the formula also be in the "blank" cells?

No it'll just look like the 2nd image

formulas will return a numeric value or "0" shown in the accounting format (ugh accountants, right?)

I'm a CPA

 

[Fluff]

Ok, how about

Sub jasonfish()
   Dim Rng As Range
   
   For Each Rng In Range("O9:O" & Range("N" & Rows.Count).End(xlUp).Row).SpecialCells(xlFormulas).Areas
      Rng.Offset(Rng.Count).Resize(1).Formula = "=sum(" & Rng.Address & ")"
   Next Rng
End Sub

 

[jasonfish11]

Ok, how about

Sub jasonfish()
   Dim Rng As Range
  
   For Each Rng In Range("O9:O" & Range("N" & Rows.Count).End(xlUp).Row).SpecialCells(xlFormulas).Areas
      Rng.Offset(Rng.Count).Resize(1).Formula = "=sum(" & Rng.Address & ")"
   Next Rng
End Sub

Wow that works perfectly.

Thank you so much for your help.