I have an excell sheet with tables in it that I want to shift down the format
one row when data is entered in the cell above. Below is an example of what
I am looking for.

Example one
a b c
1 1 2 3
2 1 4 6
3
4 1 3 2
5


Example Two when data is entered in row 3 and 5 from above example
a b c
1 1 2 3
2 1 4 6
3 1 9 7
4
5 1 3 2
6 1 2 9
7

No special case testing done in this code. If the cell below the one you
just made a change in has something in it, then a blank row is inserted. The
sheet this is used on cannot be protected.

To put the code to work, go to the sheet where this all needs to happen and
right-click on its name tab and choose [View Code] from the list. Copy the
code below and paste it into the code module that is presented to you and
then close the Visual Basic Editor window. Give it a test run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsEmpty(Target.Offset(1, 0)) Then
Application.EnableEvents = False
Application.ScreenUpdating = False
Target.Offset(1, 0).EntireRow.Insert
Application.EnableEvents = True
End If
End Sub


"Jeremy" wrote:

I have an excell sheet with tables in it that I want to shift down the format
one row when data is entered in the cell above. Below is an example of what
I am looking for.

Example one
a b c
1 1 2 3
2 1 4 6
3
4 1 3 2
5


Example Two when data is entered in row 3 and 5 from above example
a b c
1 1 2 3
2 1 4 6
3 1 9 7
4
5 1 3 2
6 1 2 9
7