EXCEL - adding a row automatically

Question

In Excel, how do I automatically add a row when I have input data into a cell in column B and hit [enter]?

Answer 1

Requires Macro (VBA code). To install the code below.

To install the Event VBA code:

1. Right click the worksheet tab name
2. Select "View code" (will open the VBA editor window at worksheet's module)
3. Copy the VBA code below and paste into the VBA editor.
4. Do NOT rename the sub
5. Edit the code for insert above or below the data entry as per my instructions below.
6. Close the VBA editor (Cross very top right of VBA editor window)
7. Save the workbook as Macro enabled.
8. Ensure macros are enabled. See help for how to do this. (Option to "Disable all macros with notification" should be OK.)

You have not indicated if the row is to be inserted above or below the data entry in column B. I have provided both options with one commented out (commented out will be green in VBA editor. You can remove the comment (single quote) from the beginning of the second line and comment out the other line if you want the inserted row above the data entry)

Private Sub Worksheet_Change(ByVal Target As Range)

    'Test if change is in column B and only one cell changed

    If Not Intersect(Target, Me.Columns("B")) Is Nothing And _

            Target.Cells.Count = 1 Then

        Target.Offset(1, 0).EntireRow.Insert Shift:=xlDown      'Insert row below data entry

        'Target.EntireRow.Insert Shift:=xlDown       'Insert row above data entry

    End If

End Sub

Answer 2

I must have had my blinders on.

It works GREAT!!!!

Thank you for all your help!

Now I don't have to keep manually inserting rows.

Thanks again!

Answer 3

Thank you for your help.

I followed your instructions, but it did not work.

I input data in the B column and hit ENTER. Nothing happened.

I am a code virgin, sorry.