how to automatically insert row when i hit enter

[Dano]

I've got a list in a1 to a10, at the bottom of the list i have a sum of a1
to a10. when hitting enter in a10, how could i automatically insert a row
so i can enter more data?

 

[Don Guillett]

try this
right click sheet tab>view code>insert this>SAVE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$10" Then Target.Rows.Insert
End Sub

 

[Gord Dibben]

Dano

"Automatically" would involve VBA code. See David McRitchie's "insertrow"
code.

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

Gord Dibben XL2002

 

[Earl Kiosterud]

Dano,

If you'll be adding more and more rows (A11, then A12, etc), this approach
might work for you.

Insert a row after your last data row.
Modify your SUM formula to include it (A1:A11).
Select A1:A11 and name it MyData
You can hide row A11. You'll not be putting anything in it. It's only
there to allow you to insert a row inside the range that your SUM formula
refers to, so it will expand automatically.

Put the following sub in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("MyData").Row + Range("MyData").Rows.Count - 2 Then
Application.EnableEvents = False
Target.Offset(1, 0).EntireRow.Insert xlUp
Application.EnableEvents = True
End If
End Sub

This won't copy down any formulas into the newly inserted row, though that
can be added. And it can get stuck if it crashes, leaving events turned
off. But see if it's what you like.