Determine whether user is adding or deleting rows

by

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

More Related Contents:

  1. delete Autofilter Field resullt using the header Name
  2. Somehow it’s not copying [closed]
  3. VBA macro that search for file in multiple subfolders
  4. So, I have 6 “master” files to then divide into 40 separate files
  5. Getting ScriptControl to work with Excel 2010 x64
  6. Application.Match gives type mismatch
  7. Count and Highlight keywords within phrases
  8. Transpose a range in VBA
  9. Protecting cells in Excel but allow these to be modified by VBA script
  10. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  11. Detect merged cells in VBA Excel with MergeArea
  12. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  13. Parse HTML content in VBA
  14. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  15. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  16. Should I turn .CutCopyMode back on before exiting my sub procedure?
  17. VBA.Find and adding a value
  18. excel vba- extract text between 2 characters
  19. Round function in Excel, worksheet function vs VBA
  20. How to continue the code on the next line in VBA
  21. Excel VBA Userform – Execute Sub when something changes
  22. Count the number of rows in another sheet [duplicate]
  23. Getting Excel to refresh data on sheet from within VBA
  24. VBA Password Input with Cancel Function
  25. Finding first blank row, then writing to it
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment