Determine whether user is adding or deleting rows

Question

I have a VBA macro that validates user entered data (I didn't use data validation/conditional formatting on purpose).

I am using Worksheet_Change event to trigger the code, the problem I am facing now is, when there are row changes. I don't know whether it is a deleting / inserting rows.

Is there anyway to distinguish between those two?

Answer 1

You could define a range name such as RowMarker =$A$1000

Then this code on your change event will store the position of this marker against it's prior position, and report any change (then stores the new position)

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

Answer 2

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lNewRowCount As Long

    ActiveSheet.UsedRange
    lNewRowCount = ActiveSheet.UsedRange.Rows.Count

    If lOldRowCount = lNewRowCount Then
    ElseIf lOldRowCount > lNewRowCount Then
        MsgBox ("Row Deleted")
        lOldRowCount = lNewRowCount
    ElseIf lOldRowCount < lNewRowCount Then
        MsgBox ("Row Inserted")
        lOldRowCount = lNewRowCount
    End If

End Sub

Also add this in the ThisWorkBook module:

Private Sub Workbook_Open()
    ActiveSheet.UsedRange
    lOldRowCount = ActiveSheet.UsedRange.Rows.Count
End Sub

And then this in its own module:

Public lOldRowCount As Long

The code assumes you have data in row 1. Note the very first time you run it you make get a false result, this is because the code needs to set the lRowCount to the correct variable. Once done it should be okay from then on in.

If you don't want to use the Public variable and worksheet open event then you could use a named range on your worksheet somewhere and store the row count (lRowCount) there.

Answer 3

Assumption: That "distinguish the two" means to distinguish adding/deleting a row from any other type of change. If you meant, how to tell if the change was an add row OR delete row, then ignore my answer below.

In the case of inserting or deleting a row, the target.cells.count will be all the cells in the row. So you can use this If statement to capture it. Notice I use cells.columns.count since it might be different for each file. It will also trigger if the user selects an entire row and hits "delete" (to erase the values) so you'll need to code a workaround for that, though...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count = Cells.Columns.Count Then
    MsgBox "Row added or deleted"
End If

End Sub