Automatically insert a row if a cell is typed into?

[greyscout]

I was working on a spreadsheet (Excel 97) and every time I would type into a cell in Column A, it would automatically insert a new row above the given cell. This was a good thing! It would keep the amount of empty rows above my typing the same. I guess, at some point, I deleted a macro or something because now it doesn't do it anymore and I'm at a loss for how to bring it back. I assume this is either a macro or an add-in. Any help would be much appreciated.

 

[Mechixx]

Scratch that last reply. I tried yours, Mechixx. After reading your reply a few times, I realized how simple it really is. I plugged yours in and I definitely like how that works better. Thank you so much. You too are the man.

Yeah, that's what i enjoy about it too! I wasn't the one who wrote the VBA so im not even sure how it works, but it works very well. And with how easy it is to set up, you can set up as many as you want as long as you change the cell names and match it to the VBA code. i find it great for tables that you are constantly entering data into as it keeps it very clean.
Another little tidbit that i do, is i either lock the named cell, or i put a text box over it and say "Enter data above this box to insert a new row, or delete a value from this column to delete a whole row" because if by chance that named cell is deleted... the VBA code wont work anymore until you've named a cell again.

Glad it wasn't too much of a hassle to set up and get working. Any more questions let me know!

 

[Roderick_E]

In looking at the code more closely, I am not sure why he used "Worksheet_SelectionChange" instead of "Worksheet_Change".
Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if a single cell is updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if column A is updated
    If Target.Column > 1 Then Exit Sub
    
'   Only run if entry is not empty
    If Trim(Target) <> "" Then
        Application.EnableEvents = False
        Rows(Target.Row).Insert
        Application.EnableEvents = True
    End If
    
End Sub

Sorry about you having to clean up my vagueness. I didn't use Change because I was in Userform mode and was thinking I didn't want it to fire when the user was in the middle of typing Anyhow, thanks for cleaning it up.

 

[greyscout]

I've been trying to edit the code to make this work on the other side of the spreadsheet, but to no avail. What I'm trying to do now is make it so when I input data in a given row, a row is inserted ABOVE said row. It's the same as what I was trying to do before, but mirrored. I'm sure this is easy to you guys, but it's missing me.

 

[Joe4]

Not sure what you mean by "other side of the spreadsheet". Can you be more detailed in that explanation?
Also, by "given row", do you mean ANY row, or are you just limiting to specific rows?

 

[greyscout]

Not sure what you mean by "other side of the spreadsheet". Can you be more detailed in that explanation?
Also, by "given row", do you mean ANY row, or are you just limiting to specific rows?

Here's what I'm trying to accomplish: I want, whenever I type in a given row, a new row to be inserted above it.

My previous request was to have a new row inserted BELOW the row I typed into and Mechixx had the perfect code. I can't seem to edit his code to work the other way.

 

[Joe4]

Try changing this line:

Range("ToolingData_Table_End").EntireRow.Insert

to:

Range("ToolingData_Table_End").Offset(-1,0).EntireRow.Insert

Note: A slight edit may need to be made if you may be typing in row 1.

 

[greyscout]

I don't think it's that simple and this again may be my fault. I'd like to keep the original code, but add another that does the same, above the row. So, the current code requires cell A5 to be named "ToolingData_Table_End" and when I type into cell A4, a new row is created below A4. What I need to add is a code that inserts a new row above cell A2 when I type into it. I hope I explained that right.

 

[Joe4]

the current code requires cell A5 to be named "ToolingData_Table_End"

So what is the naming requirement for this new process?

 

[greyscout]

So what is the naming requirement for this new process?

There is no "requirement". It could be "Go Phillies", I guess.

 

[Joe4]

You said that you want to keep the "Insert below", but want to add an "Insert above" option.
So we need a way of determining when to do which one (when to insert "above" vs. when to insert "below").
What exactly is that criteria?