Insert new row after hitting enter
 

I would like to write VBA code that basically inserts a
row in a range after someone fills in a cell and hits
enter. For example, if my range is row1:row4, and someone
enters data in row1, a new row is inserted for row2 after
entry. I have created a a worksheet that is sectioned of
into several categories and I do not want to show
numberous rows unless data entry is required -- i.e., the
section grows the more rows someone enters. Any help is
appreciated.

Insert new row after hitting enter
 

right click on sheet tabview codeinsert thissave
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
Target.Offset(1).Rows.Insert
End Sub

"Jeff" wrote in message
...
I would like to write VBA code that basically inserts a
row in a range after someone fills in a cell and hits
enter. For example, if my range is row1:row4, and someone
enters data in row1, a new row is inserted for row2 after
entry. I have created a a worksheet that is sectioned of
into several categories and I do not want to show
numberous rows unless data entry is required -- i.e., the
section grows the more rows someone enters. Any help is
appreciated.

Insert new row after hitting enter
 

Jeff,

The following code in the worksheet module (not a standard module)
will insert a row whenever data is entered into any cell.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Rows(Target.Row + 1).EntireRow.Insert
End Sub

You can use an If statement to restrict it to fire if data is entered into a
specific column:

If Target.Column = 5 Then
Rows(Target.Row + 1).EntireRow.Insert
End If
with this your user can enter into columns A to E and only after an entry in
E will the new row be inserted.

You can also add to the If statement to make sure that a blank row doesn't
already exist...

Target is the cell that was was changed.
The macro is an event macro...

--
sb
"Jeff" wrote in message
...
I would like to write VBA code that basically inserts a
row in a range after someone fills in a cell and hits
enter. For example, if my range is row1:row4, and someone
enters data in row1, a new row is inserted for row2 after
entry. I have created a a worksheet that is sectioned of
into several categories and I do not want to show
numberous rows unless data entry is required -- i.e., the
section grows the more rows someone enters. Any help is
appreciated.