calculating based on a range of variable depth (VBA)

Hi all, normally I have been able to stumble across my own solutions but am having trouble with this one. See attachment for reference. I am trying to have the excess cash flow line calculate as EBITDA less the sum of the expenses below. The first expense is always on line 6 and there are always the same number of years for this (C:J). The number of expenses is variable and could be anywhere from 3-10. This graph is being pulled from data on another page via VBA and should function without user input. This line is the only one I haven't been able to figure out.

Please point me in the right direction as I have tried this from a few direction, with little success.

Thanks for looking!

Assuming the number of rows below 6 are sequential then:

Please Login or Register  to view this content.

of course you could just use the SUM(C6:C16) and blanks would be ignored.

Thanks, but it needs to be done in vb, not excel formula, and c16 isn't necessarily the end. There could be any number of expense items.

This code will write the formula to the cell and then replace it with the values.
Depending on how the rest of your VBA is written you may need to add sheet references and calculations to the code.

Without seeing your code on how this sheet is created, this is my best guess at a solution.

Please Login or Register  to view this content.

Thanks again. There are more rows below the total line though, so that end up thing won't work. the code snippet afterward looks promising though. when i get in front of it tomorrow i'll give it a shot and see if i can work again. thanks for your help!