Add one row to range

[scottbass]

1. What I want to do is detect when a user has entered a new row just below the last row of a table.

Some code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LO As ListObject
    Dim rng As Range
    
    Set LO = Me.ListObjects(1) ' This is just an example, assume there could be > 1 table per worksheet
    Set rng = LO.Range
    '--- I want to increment this range by 1 row
    '--- So, if the old range is A1:E5, I want the new range to be A1:E6
    
    ' Did the user modify this table, or the first row below the table?
    ' I need to do one thing if the user A) modified the current table, B) trying to add a new row, or C) editing somewhere else in the worksheet
    If Not (Intersect(rng, Target) Is Nothing) Then Debug.Print LO.name
End Sub

2. Ignoring the specific example above, how can I increment a range, either row or column?

For example:

Have: Range("A5:E10")
Want V1: Range("A5:E11") ' increment by one row
Want V2: Range("A5:F10") ' increment by one column
Want V3: Range("A5:F11") ' increment both

I investigated Range.Offset but I couldn't coax it to do what I wanted.

Thanks...

 

[MickG]

Try:=

Dim Rng As Range
 Set Rng = Range("A5:E10")
 Set Rng = Rng.Resize(Rng.Rows.Count + 1, Rng.Columns.Count + 1)
 MsgBox Rng.Address

 

[scottbass]

Thanks...

More details: Within a table, what I'm trying to do is determine when a user has:

* Changed outside the table (this includes the first column to the right of the table data) (bad)
* Changed a table header (bad)
* Changed data (update)
* Changed the first row after the end of the table (insert)
* Deleted a row(s) from the table (delete)

With a table (ListObject), I've got Table.HeaderRowRange and Table.DataRowRange. So far so good. Since Table.InsertRowRange is useless (come on Microsoft!), I've got this code, which seems to work OK:

        With Tbl.Range
            Set InsertRowRange = _
            Range(Cells(.Rows.Count + .row, .Column), _
                  Cells(.Rows.Count + .row, .Columns.Count + .Column - 1)) ' Derive first row past the table
        End With

I'm using this code to detect where the user changed his data:

Function IsInside(Table As Range, Target As Range) As Integer
    Set rOverlap = Intersect(Table, Target)
    If Not rOverlap Is Nothing Then  'At least some overlap
        If rOverlap.Count = Target.Count Then
            IsInside = 0 'Target completely within Table
        Else
            IsInside = 1 'Target partially outside Table
        End If
    Else
        IsInside = -1  'Target completely outside Table
    End If
End Function

Regarding deletions, I'm using a public LastRow variable, then comparing the current last row with the previous last row, then updating the public variable. This too works OK.

However, I've put code to initialize the LastRow variable in the Worksheet_Activate event. This works great, except for when the workbook is first opened.

Is there a way I can trigger the Worksheet_Activate event for the active worksheet, when the workbook is opened? I want to leave the user where he was before, i.e. if Sheet5 was the active worksheet when he last saved the workbook, then I want Sheet5's activate event to trigger when he opens the workbook.

After that, my code is working as desired as the user navigates the other worksheet tabs.