I am struggling with one of the formulae that I want to use.

I have a formula $H$2:$H$10, I want to change this formula every time I run a VBA such that it considers each additional cell in Column H, but that additional cell has a value zero.

So I run a VBA code for $H$2:$H$10, then I change this range to $H$2:$H$11 and re-run the VBA code, then the range changes to $H$2:$H$12, and the VBA code is run again. However, there are no values in cells H3 onwards.

So H2 has the value 1, but H3, H4 and so on have values 0 and cannot be changed due to the nature of the VBA code I am running.

I will be very grateful if someone can let me know how to change this range by adding one additional cell everytime the VBA code is run, when each additional cell has the value zero.