Auto insert a row or cell

  • Thread starter Thread starter excel2000
  • Start date Start date
E

excel2000

I'm entering numbers under a colum, I need the total sum of the colum;
But after I enter the last number 221 and press enter it needs to skip
with a blank row or cell ? but allways adding the total sum at the last
cell.
After the 221 and press enter, the cursor will go to the next cell
below, which the total sum is located; I need a blank cell so it will
skip to the next cell where I will enter the next number.

Thank you very much.
JA
ex:
123
222
111
221
---> Here is where I need the blank row allways
677 This is the total sum

File Attached: http://www.excelforum.com/attachment.php?postid=329397 (book1.xls)
 
excel2000,

I didn't open your attached workbook - they are rarely if ever
needed - but you can use the code below to do that. Right click the
sheet tab, select "View Code" and paste this code into the window that
appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target(2).Formula, 5) = "=SUM(" Then Target(2).Insert (xlDown)
End Sub


excel2000 said:
I'm entering numbers under a colum, I need the total sum of the colum;
But after I enter the last number 221 and press enter it needs to skip
with a blank row or cell ? but allways adding the total sum at the last
cell.
After the 221 and press enter, the cursor will go to the next cell
below, which the total sum is located; I need a blank cell so it will
skip to the next cell where I will enter the next number.

Thank you very much.
JA
ex:
123
222
111
221
---> Here is where I need the blank row allways
677 This is the total sum

File Attached:
Click to expand...
http://www.excelforum.com/attachment.php?postid=329397 (book1.xls)
 
I should have noted that this requires that you have the option to
auto extend lists and formulas turned on (Excel 2000 and up).

HTH,
Bernie
MS Excel MVP

Bernie Deitrick said:
excel2000,

I didn't open your attached workbook - they are rarely if ever
needed - but you can use the code below to do that. Right click the
sheet tab, select "View Code" and paste this code into the window that
appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target(2).Formula, 5) = "=SUM(" Then Target(2).Insert (xlDown)
End Sub


excel2000 said:
I'm entering numbers under a colum, I need the total sum of the colum;
But after I enter the last number 221 and press enter it needs to skip
with a blank row or cell ? but allways adding the total sum at the last
cell.
After the 221 and press enter, the cursor will go to the next cell
below, which the total sum is located; I need a blank cell so it will
skip to the next cell where I will enter the next number.

Thank you very much.
JA
ex:
123
222
111
221
---> Here is where I need the blank row allways
677 This is the total sum

File Attached:
Click to expand...
http://www.excelforum.com/attachment.php?postid=329397 (book1.xls)
Click to expand...
 
Consider evaluating XL2003. The list functionality is greatly improved and
includes a feature akin to what you describe.

/i.
 
Back
Top