VBA: Intersect and Insert Row doesn't work

[Janb]

Hi,

I'm trying to insert a row below the active row when the value of a cell changes. I tried it on different ways and everytime with the same result and error.

Excel inserts 90 rows (instead of 1) and after that, Excel crashes everytime with the following error:

Run-time error '-2147417848 (80010108)': Method "Insert"of object "Range" failed

The code I'm using

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngLR As Long
    Dim sht As Worksheet
   
    Set sht = ActiveWorkbook.ActiveSheet
   
    lngLR = sht.Cells(Rows.Count, "A").End(xlUp).Row
   
    If Not Intersect(Target, sht.Range("C2:C" & lngLR)) Is Nothing Then
        Target.Offset(1, 0).EntireRow.Insert
    End If

End Sub

I have replace "Target.Entire......." with rows(activecell.row) but without any success.

Can someone help me with this problem?

Cheers,
Jan

 

[mikerickson]

Have you tried turning Events off for the insert

If Not Intersect(Target, sht.Range("C2:C" & lngLR)) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(1, 0).EntireRow.Insert shift:= xlDown
    Application.EnableEvents = True
End If

 

[Janb]

Dear mikerickson,

Thank you so much!!

My three-hour frustration has disappeared in one go with your answer (solution).

I'm now going to dive in the fact why EnableEvents is necessary.

Cheers,
Jan

 

[Sunjinsak]

I'm now going to dive in the fact why EnableEvents is necessary.

It's because your code is triggered on the worksheet_change() event. The line:

Target.Offset(1, 0).EntireRow.Insert shift:= xlDown

triggers the worksheet_change() event again... which calls your code again... which triggers the event again. You see the problem. It's called an 'event cascade' and is usually something you want to avoid.

Setting Application.EnableEvents = False before taking any actions that would trigger the worksheet_change() event lets you keep control. Just remember to set Application.EnableEvents back to True again once you've taken all necessary actions (as in @mikerickson's example above) so that your code will continue to fire on the next legitimate worksheet_change() event.

 

[Fluff]

Another option would be

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngLR As Long
    Dim sht As Worksheet
    If Target.CountLarge > 1 Then Exit Sub
    Set sht = ActiveWorkbook.ActiveSheet
   
    lngLR = sht.Cells(Rows.Count, "A").End(xlUp).Row
   
    If Not Intersect(Target, sht.Range("C2:C" & lngLR)) Is Nothing Then
        Target.Offset(1, 0).EntireRow.Insert
    End If

End Sub

that way if more than one cell is changed the code will exit.

 

[Janb]

Thank you Fluff. I will study and try your code. Your additonal comment is really appreciate.

edit: after reading Sunjinsak his explanation about the enableevents part, I also understand your code. Really great alternative.

Cheers Jan

 

[Fluff]

You're welcome & thanks for the feedback

 

[Janb]

It's because your code is triggered on the worksheet_change() event. The line:

Target.Offset(1, 0).EntireRow.Insert shift:= xlDown

triggers the worksheet_change() event again... which calls your code again... which triggers the event again. You see the problem. It's called an 'event cascade' and is usually something you want to avoid.

Setting Application.EnableEvents = False before taking any actions that would trigger the worksheet_change() event lets you keep control. Just remember to set Application.EnableEvents back to True again once you've taken all necessary actions (as in @mikerickson's example above) so that your code will continue to fire on the next legitimate worksheet_change() event.

Dear Sunjinsak,

Thank you so much for the explanation. Very valuable.

edit: after reading your explanation twice, it has become completely clear now why I had my problem. So thanks again!

Cheers Jan

 

[Sunjinsak]

You're welcome, glad I could help.