Calculating SubTotal of part of a column when first cell is blank

[allank]

I have a large spreadsheet with many groupings of rows, and many columns with group Subtotals. Using VBA I am trying to set the subtotal formula by detecting the blank cell at the top of the column part. Here is an example of a group:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Header[/TD]
[TD][blank cell][/TD]
[TD][blank cell][/TD]
[/TR]
[TR]
[TD]item 1[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]item 2[/TD]
[TD]25[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]item 3[/TD]
[TD]45[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]SubTotal[/TD]
[TD]=formula?[/TD]
[TD]=formula?[/TD]
[/TR]
</tbody>[/TABLE]

In the excel spreadsheet, the formula is, e.g., =SUM(B2:B4) originally created manually. The problem is that rows are added to the group, and not always in the middle of the group (in which case Excel adjusts the formula accordingly), so when a row is added at the beginning or end of the group, the Subtotal must be manually changed to include this new row. There are hundreds of these group subtotals, so manual change is a pain the butt.

I can go through the sheet (with VBA) and detect the Subtotal rows, and I know the column numbers for which the Subtotal is calculated, I just need to find the range to include in the =SUM() formula.

Does anyone have a quick and dirty way to do this? By the way, there could be additional blank cells above the blank cell shown in the example.

Any help is appreciated.
Cheers.

 

[allank]

Just to clarify, groups appear one after the other, like this example, and the SubTotal formula should be =SUM([blank cell]:[subtotal row -1]) for each affected column.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Group 1 Header[/TD]
[TD][blank cell][/TD]
[TD][blank cell][/TD]
[/TR]
[TR]
[TD]item 1[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]item 2[/TD]
[TD]25[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]item 3[/TD]
[TD]45[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]SubTotal[/TD]
[TD]=formula?[/TD]
[TD]=formula?[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Group 2 Header[/TD]
[TD][blank cell][/TD]
[TD][blank cell][/TD]
[/TR]
[TR]
[TD]item 1[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]item 2[/TD]
[TD]25[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]item 3[/TD]
[TD]45[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]SubTotal[/TD]
[TD]=formula?[/TD]
[TD]=formula?[/TD]
[/TR]
</tbody>[/TABLE]

Cheers.