execute code when row is inserted

Mase · Aug 5th 2004

I'm using Worksheet_Change in my Worksheet to react to the changes made by the user, but how do I know if the user just inserted a new row?
Or is there any on_row_change event or something like that?

jindon · Aug 5th 2004

Hi

Worksheet_Change event shoud recognize

try following code and insert a row somewhere

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("a1").Value = Range("a1").Value + 1
Application.EnableEvents = True
End Sub

Mase · Aug 6th 2004

It seems it wasn't as easy as I thought. Finally I found this script which detects if a row is inserted or deleted:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Static lastValue As Variant
Static lastRow As Long
Dim lastVal As Variant
Dim lastRw As Long
lastRw = Cells(65536, 1).End(xlUp).Row
lastVal = Cells(lastRw, 1).Value
If lastRw = lastRow Then
    lastValue = lastVal
    Exit Sub
Else
    If lastVal = lastValue Then
        Worksheets("Hoja2").Range("A1").Value = 1
        MsgBox "You aren't permitted to insert or delete rows within the dynamic range. Please undo any row insertions!"
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    Else
        lastValue = lastVal
        lastRow = lastRw
    End If
End If
End Sub

Display More

Thank's anyway

execute code when row is inserted

Participate now!

Share