How to tell if user is deleting or inserting rows - Excel VBA

Question

I am in a situation where the columns on one Worksheet need to track with the rows on another Worksheet. So, if a user deletes a column (which they must be allowed to do), I need the corresponding row in another worksheet to be deleted. That works fine, and the code below accomplishes the task.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Target.EntireRow.Address Then
        Debug.Print Target.Address
        Sheet2.Range("A1").Offset(, Target.Row - 1).EntireColumn.Delete
    End If
End Sub

However, this same code runs when a user inserts a row, while in fact I need to add a row to Sheet2 in that case. How can I tell if the user is inserting or deleting when a row action is taken?

[Here](http://stackoverflow.com/questions/12064439/vba-worksheet-change-detecting-actual-cell-change) is a link discussing how to tell what actually changes. Main point is change event doesn't track what changed just that it did change, so the accepted answer was keep a mirrored sheet to compare against to tell what changed. — gtwebb, Jun 13 '16 at 17:08

score 3 · Accepted Answer · edited May 23 '17 at 11:52

Since I can't add it as a comment yet, I believe this was already answered here

Basically, you would define a range name, and then call that range name on Worksheet_Change. Based on whether the range has moved and which direction, you should be able to determine whether columns have been added or removed, and then process accordingly.

Brettdj's code from the above-linked post (For rows, but easily changable for columns):

You could define a range name such as 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

Great, the link you posted worked well for my situation – corbfon Jun 13 '16 at 17:49 — corbfon, Jun 13 '16 at 17:49

How to tell if user is deleting or inserting rows - Excel VBA

1 Answers1