Add one row to range


New Member
Sep 3, 2012
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
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.


Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Dim Rng As Range
 Set Rng = Range("A5:E10")
 Set Rng = Rng.Resize(Rng.Rows.Count + 1, Rng.Columns.Count + 1)
 MsgBox Rng.Address
Upvote 0

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
            IsInside = 1 'Target partially outside Table
        End If
        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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back