Determine whether user is adding or deleting rows
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?
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
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.
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