Excel VBA HELP needed! Capturing Adding and Deleting of Rows on spreadsheet.

Hi nex2022 and welcome to ExcelForum.com,

See the first answer in the following thread which seems to be an excellent implementation of your add/delete problem. However it needs a 'Named Range' on each sheet to identify whether there was an addition or a deletion. http://stackoverflow.com/questions/7...-deleting-rows

Lewis


Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

The sample code worked but was incomplete, because the value of the 'last row' had to be initialized. Otherwise, the first addition or deletion would not have been recognized. See the attached file that contains the following code:

This Workbook module - for initialization:

Option Explicit

Private Sub Workbook_Open()

  'Identify the last row in the Named Range
  'Store the value in the Global variable defined in the 'Sheet1' module
  Sheet1.lngRow = ThisWorkbook.Names("RowMarker").RefersToRange.Row

  'Put the focus on Sheet 2
  Sheets("Sheet2").Select
  Range("A1").Select
End Sub

Sheet1 module:

Option Explicit

'Global variable that stores the 'last row' in NAMED RANGE 'RowMarker'
Public lngRow As Long

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng1 As Range
    
    'Obtain the range referred to by the NAMED RANGE 'RowMarker'
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    
    'The following lines re-intialize the range if the global 'lngRow' gets reset to ZERO
    'This usually happens during debugging and should not happen in normal operation
    'If this happens addition/deletion of lines will not be identified
    If lngRow = 0 Then
        lngRow = rng1.Row
        MsgBox "Data Integrity Error. Global variable 'lngRow' had a value of ZERO."
        Exit Sub
    End If
    
    
    'Do nothing - the number of rows is the same
    If rng1.Row = lngRow Then
      Exit Sub
    End If
    
    'Identify whether rows were added or deleted
    If rng1.Row < lngRow Then
        MsgBox lngRow - rng1.Row & " rows removed"
    Else
        MsgBox rng1.Row - lngRow & " rows added"
    End If
    
    'When rows are added or deleted the NAMED RANGE automatically gets adjusted
    'The following global variable must now be adjusted to match the NEW 'last row' in the range
    lngRow = rng1.Row
    
End Sub

Lewis

Hi,

Nice worksheet design.

See the attached file. You will have to copy the code from my file to yours, since my Excel 2003 loses some of the features (e.g. colors, controls, etc) that are not available in Excel 2003.

The NAMED RANGE is now automatically defined/modified when you open the workbook.

ThisWorkBook Module (changes in red):

Private Sub Workbook_Open()
  Dim Evnt As String
  Evnt = "Open"
  Call Elog(Evnt)

  'This adds or updates NAMED RANGE 'RowMarker' on 'Sheet1'
  '
  'The same thing can be done manually as follows (Excel 2003):
  'Insert > Name > Define (allows add, change or delete)
  
  'Adds or moves the named range (Change the last number to be more than the last row that will ever be used)
  Names.Add ("RowMarker"), Sheet1.Range("$A$1000")
  
  'Identify the last row in the Named Range
  'Store the value in the Global variable defined in the 'Sheet1' module
  'The 'NAMED RANGE' must be pre-defined or a RUNTIME ERROR will occur
  Sheet1.lngRow = ThisWorkbook.Names("RowMarker").RefersToRange.Row
  

End Sub

Sheet1 Module (Summary Sheet) - all code is new:

Option Explicit

Public lngRow As Long

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng1 As Range
    Dim Evnt As String

   'Obtain the range referred to by the NAMED RANGE 'RowMarker'
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    
    'The following lines re-intialize the range if the global 'lngRow' gets reset to ZERO
    'This usually happens during debugging and should not happen in normal operation
    'If this happens addition/deletion of lines will not be identified
    If lngRow = 0 Then
        lngRow = rng1.Row
        MsgBox "Data Integrity Error. Global variable 'lngRow' had a value of ZERO."
        Exit Sub
    End If
    
    
    'Do nothing - the number of rows is the same
    If rng1.Row = lngRow Then
      Exit Sub
    End If
    
    'Identify whether rows were added or deleted
    If rng1.Row < lngRow Then
        'MsgBox lngRow - rng1.Row & " rows removed"
        Evnt = lngRow - rng1.Row & " row(s) removed"
        Call Elog(Evnt)
    Else
        'MsgBox rng1.Row - lngRow & " rows added"
        Evnt = rng1.Row - lngRow & " row(s) added"
        Call Elog(Evnt)
    End If
    
    'When rows are added or deleted the NAMED RANGE automatically gets adjusted
    'The following global variable must now be adjusted to match the NEW 'last row' in the range
    lngRow = rng1.Row
    
End Sub

Lewis

Hey Lewis,

Thank you so much!!! Finally something that's complete and could track if people aren't following instructions!!!!!!! This is gonna save me and give me people that can't follow simple instructions!!! Thanks again Lewis and greatly appreciated!