Trigger events with the insertion and deletion of rows in VBA Excel-CodePudding

I am using the code below to trigger certain events when rows have been deleted and inserted, the good thing about this code is that it makes a distinction between inserted and deleted rows and thus trigger events for each situation, the only limitation with this is it doesn't work if the first change you make to the workbook is the addition or deletion of a row, but it works fine after that first change. Anyone know how to fix this problem? Someone suggested a Worksheet_Activate event before the code but i am too inexperienced to figure it out...

First we define a range name (RowMarker =$A$1000)

Private Sub Worksheet_Change(ByVal Target As Range)
    Static lngRow As Long
    Dim rng1 As Range
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    If lngRow = 0 Then
    lngRow = rng1.Row
        Exit Sub
    End If
    If rng1.Row = lngRow Then Exit Sub
    If rng1.Row < lngRow Then
        MsgBox lngRow - rng1.Row & " rows removed"
    Else
        MsgBox rng1.Row - lngRow & " rows added"
    End If
    lngRow = rng1.Row
End Sub

CodePudding user response：

Please, copy the next code in the sheet code module, to be processed (overwrite the existing code event):

Option Explicit

Private Sub Worksheet_Activate()
   Dim rng1 As Range
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    ThisWorkbook.lRow = rng1.row
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Static lngRow As Long
    Dim rng1 As Range
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    If lngRow = 0 Then lngRow = ThisWorkbook.lRow
    If lngRow = 0 Then 'only for the case you copied the code and forgot sheet activating...
        lngRow = rng1.row
        Exit Sub
    End If
    If rng1.row = lngRow Then Exit Sub
    If rng1.row < lngRow Then
        MsgBox lngRow - rng1.row & " rows removed"
    Else
        MsgBox rng1.row - lngRow & " rows added"
    End If
    lngRow = rng1.row
End Sub

1.1 Copy this code in ThisWorkbook code module:

Public lRow As Long

Private Sub Workbook_Open()
   Dim rng1 As Range
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    lRow = rng1.row
End Sub

Deactivate the active sheet (go to other one) and activate it (come back). Only first time! When you open the workbook and go to that specific sheet, the event is automatically triggered.
Try the solution and send some feedback

lRow can be used without the static variable, too, but I tried to keep your initial code as it is, only modifying it to have a reference first time when Change event is triggered.

CodePudding user response：

Add Workbook event

Private Sub Workbook_Open()
    Sheet1.lrow = Sheet1.Range("RowMarker").Row
End Sub

Worksheet Event

Public lrow As Long

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim n as Long
    n = Me.Range("RowMarker").Row - lrow
    lrow = Me.Range("RowMarker").Row
    If n > 0 Then
        MsgBox n & " rows inserted"
    ElseIf n < 0 Then
        MsgBox n & " rows removed"
    End If

End Sub