Excel. How do I insert a new row by hitting enter.

Posted by Carl on January 19, 2002 11:40 AM

In MS Excel. I would love to know how to have a row inserted when I hit enter. When filing out a formatted excel sheet, I would like it to add rows as needed. ie, I would only format the first(top) row, and then enter data, if I had more data to enter in the row below I would simply hit ENTER, at which point the row below would contain the same formulas as the one above(exact copy)but blank. This would help in creating sheets where you don't know how many rows you will use.

Posted by Jacob on January 19, 2002 2:04 PM

Try this

Put this in the workbook code

Private Sub Workbook_Open()
Application.OnKey ("~"), "InsertIt"
End Sub

Put this in a module

Sub InsertIt()
ActiveCell.Insert Shift:=xlDown
End Sub

Use onkey "~" For the keyboard enter and onkey"{ENTER}" For the number keypad enter.

HTH

Jacob

Posted by Jacob on January 19, 2002 2:05 PM

One More Thing

Try this

Put this in the workbook code

Private Sub Workbook_Close()
Application.OnKey ("~"), ""
End Sub

To turn off the onkey after you are done. You should also put these in the workbook activate and deactivate code as well.

HTH

Jacob