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 p开发者_开发技巧urpose).

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.

After searching for a bit decided to solve it myself. In your Worksheet module (e.g. Sheet1 under Microsoft Excel Objects in VBA Editor) insert the following:

Private usedRowsCount As Long 'use private to limit access to var outside of sheet

'Because select occurs before change we can record the current usable row count
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  usedRowsCount = Target.Worksheet.UsedRange.rows.count 'record current row count for row event detection
End Sub

'once row count recorded at selection we can compare the used row count after change occurs
'with the Target.Address we can also detect which row has been added or removed if you need to do further mods on that row
Private Sub Worksheet_Change(ByVal Target As Range)
  If usedRowsCount < Target.Worksheet.UsedRange.rows.count Then
    Debug.Print "Row Added: ", Target.Address
  ElseIf usedRowsCount > Target.Worksheet.UsedRange.rows.count Then
    Debug.Print "Row deleted: ", Target.Address
  End If
End Sub

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

Some of what your end purpose of distinguishing between insertions and deletions ends up as will determine how you want to proceed once an insertion or deletion has been identified. The following can probably be cut down substantially but I have tried to cover every possible scenario.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim olr As Long, nlr As Long, olc As Long, nlc As Long

    With Target.Parent.Cells
        nlc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        nlr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.Undo    'undo the last change event
        olc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        olr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.Repeat  'redo the last change event
    End With

    If nlr <> olr Or nlc <> olc Then
        Select Case nlr
            Case olr - 1
                Debug.Print "One (1) row has been deleted"
            Case Is < (olr - 1)
                Debug.Print (olr - nlr) & " rows have been deleted"
            Case olr + 1
                Debug.Print "One (1) row has been inserted"
            Case Is > (olr + 1)
                Debug.Print (nlr - olr) & " rows have been inserted"
            Case olr
                Debug.Print "No rows have been deleted or inserted"
            Case Else
                'don't know what else could happen
        End Select
        Select Case nlc
            Case olc - 1
                Debug.Print "One (1) column has been deleted"
            Case Is < (olc - 1)
                Debug.Print (olc - nlc) & " columns have been deleted"
            Case olc + 1
                Debug.Print "One (1) column has been inserted"
            Case Is > (olc + 1)
                Debug.Print (nlc - olc) & " columns have been inserted"
            Case olc
                Debug.Print "No columns have been deleted or inserted"
            Case Else
                'don't know what else could happen
        End Select
    Else
        'deal with standard Intersect(Target, Range) events here
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Essentially, this code identifies the last cell column-wise and the last cell cell row-wise. It then undoes the last operation and checks again. Comparing the two results allows it to determine whether a row/column has been inserted/deleted. Once the four measurements have been taken, it redoes the last operation so that any other more standard Worksheet_Change operations can be processed.

There are two a bit another approaches both based on the following template.

1. Define a module or class module variable of Range type.
2. “Pin” a special range by assigning it to the variable using absolute address and save its address or size (it depends on approach).
3. To determine a subtype of user action manipulate with the variable in a sheet change event handler.

In the first approach the whole range of interest is assigned to the variable and range's size is saved. Then in a sheet change event handler the following cases must be processed:

• an exception occurs when accessing Address property => the pinned range is no longer exist;
• the address of changed cell is below then pinned range => an insertion was => update the variable
• a new size of the pinned range is different from saved (smaller => something was deleted, bigger => something was inserted).

In the second approach a “marker” range is assigned to the variable (see example below) and the range address is saved in order to determine movements or shifts in any direction. Then in a sheet change event handler the following cases must be processed::

• an exception occurs when accessing Address property => the pinned “marker” range is no longer exist;
• the address of changed cell is below then "marker" range => an insertion was => update the variable
• there is a difference in any direction, i.e. abs(new_row - saved_row) > 0 or abs(new_col-saved_col) > 0 => the pinned range was moved or shifted.

Pros:

• User-defined name is not used
• UsedRange property is not used
• A pinned range is updated accordingly to user actions instead of assumption that a user action will not occur below 1000-th row.

Cons:

• The variable must be assigned in a workbook open event handler in order to use it in a sheet change event handler.
• The variable and a WithEvents-variable of object must be assigned to Nothing in a workbook close event handler in order to unsubscribe form the event.
• It is impossible to determine sort operations due to they change value of range instead of exchange rows.

The following example shows that both approaches could work. Define in a module:

Private m_st As Range
Sub set_m_st()
  Set m_st = [$A$10:$F$10]
End Sub
Sub get_m_st()
  MsgBox m_st.Address
End Sub

Then run set_m_st (simply place a cursor in the sub and call Run action) to pin range $A$10:$F$10. Insert or delete a row or cell above it (don't confuse with changing cell(s) value). Run get_m_st to see a changed address of the pinned range. Delete the pinned range to get "Object required" exception in get_m_st.

Capture row additions and deletions in the worksheet_change event.

I create a named range called "CurRowCnt"; formula: =ROWS(Table1). Access in VBA code with:

CurRowCnt = Evaluate(Application.Names("CurRowCnt").RefersTo)

This named range will always hold the number of rows 'after' a row(s) insertion or deletion. I find it gives a more stable CurRowCnt than using a global or module level variable, better for programming, testing and debugging.

I save the CurRowCnt to a custom document property, again for stability purposes.

ThisWorkbook.CustomDocumentProperties("RowCnt").Value = Evaluate(Application.Names("CurRowCnt").RefersTo)

My Worksheet_Change Event structure is as follows:

Dim CurRowCnt as Double
CurRowCnt = Evaluate(Application.Names("CurRowCnt").RefersTo)
Select Case CurRowCnt

    '' ########## ROW(S) ADDED
     Case Is > ThisWorkbook.CustomDocumentProperties("RowCnt").Value
         Dim r As Range
         Dim NewRow as Range

         ThisWorkbook.CustomDocumentProperties("RowCnt").Value = _
         Evaluate(Application.Names("CurRowCnt").RefersTo)

         For Each r In Selection.Rows.EntireRow
             Set NewRow = Intersect(Application.Range("Table1"), r)
             'Process new row(s) here
         next r

    '' ########## ROW(S) DELETED
     Case Is < ThisWorkbook.CustomDocumentProperties("RowCnt").Value

         ThisWorkbook.CustomDocumentProperties("RowCnt").Value = _
         Evaluate(Application.Names("CurRowCnt").RefersTo)

         'Process here

    '' ########## CELL CHANGE
    'Case Is = RowCnt

        'Process here

    '' ########## PROCESSING ERROR
    Case Else 'Should happen only on error with CurRowCnt or RowCnt
        'Error msg here

End Select