This will capture when any row less than row 2000 is deleted, when put in the sheet's code module. However, the Calculate event won't be triggered unless there is a formula on the sheet.
Private Sub Worksheet_Calculate()
If Range("oneColumn").Row < 2000 Then
MsgBox "row deleted"
End If
Range("2000:2000").Name = "oneColumn"
End Sub
If the formula =COUNTA(A:A) is somewhere on the page, this will trigger when a row is either added or deleted above row 2000.
Private Sub Worksheet_Calculate()
If Range("oneColumn").Row < 2000 Then
MsgBox "row deleted"
End If
If Range("oneColumn").Row > 2000 Then
MsgBox "row add"
End If
Range("2000:2000").Name = "oneColumn"
End Sub
The key to this is finding the functions to trigger the Calculate event. COUNTA(A:A) is excelent since it recalculates whenever a cell is removed or added to column A.
Bookmarks