Automatically insert row below cell when text is written in.

[nickstar76]

Hey there

I know a little about Macro's but i have not been able to find the details online so far. What I want is for any information that is entered into column J for that to automatically insert a row underneath that row. The main problem i seem to be having is I can find a macro but you obviously need to click on tools>macro>run macro which goes against the whole point of it being automatic. The rows involved are 3:500.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
    With Target(1, 7)
        .Value = Date
        .EntireColumn.AutoFit
    End With
ElseIf Target.Column = 10 Then
    If Target.Value <> "" And Target.Offset(1) <> "" Then Target.Offset(1).EntireRow.Insert
End If
End Sub

 

[nickstar76]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
    With Target(1, 7)
        .Value = Date
        .EntireColumn.AutoFit
    End With
ElseIf Target.Column = 10 Then
    If Target.Value <> "" And Target.Offset(1) <> "" Then Target.Offset(1).EntireRow.Insert
End If
End Sub

Now it seems that the code is not working anymore. No error messages or anything just when entering data in that cell nothing happens.

 

[VoG]

It is working here. Perhaps events are disabled. In the code window press CTRL + G to open the Immediate Window, type in

Application.EnableEvents=True

and press Enter. Then try entering data in column J.

 

[nickstar76]

It is working here. Perhaps events are disabled. In the code window press CTRL + G to open the Immediate Window, type in

Application.EnableEvents=True

and press Enter. Then try entering data in column J.

I have entered that data but still nothing is working. If i go back to your old code that still works.

 

[VoG]

Odd. Try saving your work, close Excel then restart it.

 

[nickstar76]

Tried that and still nothing. I would usually be very happy with the previous code but I can see people I work with accidently hitting that tab and entering in row after row and they don't know how to delete them.

 

[VoG]

I don't know what the problem is. Here is my demo workbook http://www.box.net/shared/g4qajizmnu

 

[nickstar76]

http://www.box.net/shared/ls4b51jdtl

That is mine maybe this will help more so?

 

[VoG]

This should fix it

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
    With Target(1, 7)
        .Value = Date
        .EntireColumn.AutoFit
    End With
ElseIf Target.Column = 10 Then
    If Target.Value <> "" And Target.Offset(1, -8).Value <> "" Then Target.Offset(1).EntireRow.Insert
End If
End Sub

 

[nickstar76]

This should fix it

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
    With Target(1, 7)
        .Value = Date
        .EntireColumn.AutoFit
    End With
ElseIf Target.Column = 10 Then
    If Target.Value <> "" And Target.Offset(1, -8).Value <> "" Then Target.Offset(1).EntireRow.Insert
End If
End Sub

Awesome work thank you so much now all I just need to change it to the column L now how would I do that.