[SOLVED] Ignore change when row deleted, to bypass Worksheet

Ignore change when row deleted, to bypass Worksheet_Change

The following VBA code automatically timestamps when I edit or change cells.

However, when I delete a row it will cause the cell directly under it to refresh its timestamp. This is annoying and has led me to hide unwanted rows instead of deleting them.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Solution

You can ignore changes which involve a whole row or rows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range, c As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    
    'whole row(s) changed - do not process
    If Target.Columns.Count = Me.Columns.Count Then Exit Sub
    
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    xOffsetColumn = 1
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            Set c = Rng.Offset(0, xOffsetColumn) '****
            If Not VBA.IsEmpty(Rng.Value) Then
                c.Value = Now
                c.NumberFormat = "dd-mm-yyyy, hh:mm:ss"
            Else
                c.ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub