Add cells between blank cells
 

I have numbers in cells I2:I20, cell I21 is blank and then more numbers in
cells I22:I30 and I31 is blank. This will repeat down the sheet. I would
like a formula in column J to sum all the numbers in between the blank cells.
In cell J20 I would like it to sum cells I2:I20, in cell J30 sum cells
I22:I30, etc. I know I can go to those cells and enter the sum equation but
is there something I can copy down the sheet? Let me know if you don't
understand and I will try to rephrase the question. Thanks.

Add cells between blank cells
 

Sub AddFormulas()
j = Cells(Rows.Count, "I").End(xlUp).Row + 1
k = 2
For i = 1 To j
If Cells(i, "I").Value = "" Then
Cells(i, "J").Formula = "=SUM(I" & k & ":I" & i - 1 & ")"
k = i + 1
End If
Next
End Sub


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Gary''s Student - gsnu200856


"Jambruins" wrote:

I have numbers in cells I2:I20, cell I21 is blank and then more numbers in
cells I22:I30 and I31 is blank. This will repeat down the sheet. I would
like a formula in column J to sum all the numbers in between the blank cells.
In cell J20 I would like it to sum cells I2:I20, in cell J30 sum cells
I22:I30, etc. I know I can go to those cells and enter the sum equation but
is there something I can copy down the sheet? Let me know if you don't
understand and I will try to rephrase the question. Thanks.

Add cells between blank cells
 

Enter in J2 and fill down...
=IF(ISBLANK(I2),SUM($I$2:I2)-SUM($J$1:OFFSET($J$1,ROW()-2,0)),"")
--
Jim Cone
Portland, Oregon USA



"Jambruins"
wrote in message
I have numbers in cells I2:I20, cell I21 is blank and then more numbers in
cells I22:I30 and I31 is blank. This will repeat down the sheet. I would
like a formula in column J to sum all the numbers in between the blank cells.
In cell J20 I would like it to sum cells I2:I20, in cell J30 sum cells
I22:I30, etc. I know I can go to those cells and enter the sum equation but
is there something I can copy down the sheet? Let me know if you don't
understand and I will try to rephrase the question. Thanks.

Add cells between blank cells
 

thank you

"Jim Cone" wrote:

Enter in J2 and fill down...
=IF(ISBLANK(I2),SUM($I$2:I2)-SUM($J$1:OFFSET($J$1,ROW()-2,0)),"")
--
Jim Cone
Portland, Oregon USA



"Jambruins"
wrote in message
I have numbers in cells I2:I20, cell I21 is blank and then more numbers in
cells I22:I30 and I31 is blank. This will repeat down the sheet. I would
like a formula in column J to sum all the numbers in between the blank cells.
In cell J20 I would like it to sum cells I2:I20, in cell J30 sum cells
I22:I30, etc. I know I can go to those cells and enter the sum equation but
is there something I can copy down the sheet? Let me know if you don't
understand and I will try to rephrase the question. Thanks.

I tried this but the total shows in cell j21 or j31 rather than j20 or j30.. Is there an adjustment that I can make to this vb code to make that occur?

thx...

Snowiii33