VBA to expand a sum formula based on if there is a value in another cell

[ExcelDropper]

In a worksheet, I have 10 different sum formulas across a1:j1. The sum formula in a1 is =sum(a2:a5), while the sum formula in b1 is =sum(b2:b5), c1 is =sum(c2:c5) and so on for all the formulas. What I want to be able to do is expand on these formulas if there is a non-empty cell in a column or range. For example, let's say I have one value in z1 and thats all. The formulas will stay the same. But if i have a value in z1, and a value in z2, the sum formulas will expand. The sum formula in a1 is now =sum(a2:a5)+sum(a3:a6), while the sum formula in b1 is now =sum(b2:b5)+sum(b3:b6). If I had three values in column z, in z1, z2 and z3, my formulas would change again. Now a1 is =sum(a2:a5)+sum(a3:a6)+sum(a4:a7). If I removed two of the values and now only z1 has a value, it will become =sum(a2:a5) again after I click the macro.

Is this possible with VBA?

Thanks.

 

[Michael M]

Put this in A1, then drag across as far as required

=SUM(A2,(IF(COUNT(A3:A999)>0,OFFSET(A3,0,0,COUNT(A3:A999)),0)))