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.

 

[Roderick_E]

Maybe?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Trim(Cells(Target.Row - 1, 1)) <> "" Then 'if the row above current is <> ""
Rows(Target.Row - 1).Insert
End If
End Sub

 

[greyscout]

No such luck, but I'm not the most educated on Modules and Macros. I wouldn't even know if I entered it properly. I Alt+F11, then copied and pasted your code into the VBA module editor. Not sure what else I may have to do. Thank you for the reply.

 

[Joe4]

What Roderick gave you was Event Procedure VBA codee, which runs automatically, but ONLY if placed in the correct place. This needs to be placed in the Worksheet module that you want to apply it to.
You can do the following to get there:
1. Right-click on the sheet tab name at the bottom of your screen.
2. Select "View Code"
3. Paste the code into the VB Editor window that pops-up

That should be all that you need to do.

 

[greyscout]

Here is a screenshot of what I did. Not sure if I'm still doing something wrong. Should I save it once it's in there, or just close VBA? If I input it correctly, it's not doing anything. I'm not complaining. It's not a time-sensitive thing. This would just make my job a lot easier during my busy season.

[/IMG]

 

[Joe4]

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

 

[Mechixx]

Hey there, so I've found a solution to this awhile back, and forget where i got it from but here's what i use all the time for inputting data into tables. This code is also very useful because if you delete a cell in the specified column, it deletes the whole row which is awesome, keeps things very tidy.

All you have to do is name a cell, 2 cells down from where your data is, so that there's a blank cell in between for entering data.

So in my example ive named the cell "ToolingData_Table_End" and everywhere you see that in this code, change to whatever you have named your cell. then wherever you want this code to work, just right click the worksheet tab, select view code, and paste this macro in there, and voila! it'll start working right away

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rCell                       As Range
   Dim rDelete                     As Range
   If Not Intersect(Target, Range("ToolingData_Table_End").EntireColumn) Is Nothing Then

      Application.EnableEvents = False
      For Each rCell In Intersect(Target, Range("ToolingData_Table_End").EntireColumn).Cells

         If rCell.Row = Range("ToolingData_Table_End").Row - 1 Then
            If Len(rCell.Value) > 0 Then
               Range("ToolingData_Table_End").EntireRow.Insert
            Else
               If rDelete Is Nothing Then
                  Set rDelete = rCell
               Else
                  Set rDelete = Union(rDelete, rCell)
               End If
            End If
         ElseIf Len(rCell.Value) = 0 Then
            If rDelete Is Nothing Then
               Set rDelete = rCell
            Else
               Set rDelete = Union(rDelete, rCell)
            End If
         End If

      Next rCell
      If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
      Application.EnableEvents = True

   End If
End Sub

 

[greyscout]

BOOM! That did it. You're the man, Joe.

Mechixx, thanks, but my brain is having a hard time understanding this as is. I see that Joe's code is shorter than yours. Therefore, in an attempt to save what few brain cells I have left, I'm going to have to skip your code. No offense and thank you for your input. Maybe after a little ginkgo biloba, I'll check your code out again.

 

[Joe4]

You are welcome.

I documented each step, just to explain what is going on in case you ever need to change it (or debug if it is not working as expected).

 

[greyscout]

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.