How to detect if the cell being edited belongs to the last row of a defined name range

Question

I have a defined name call "Input_Range". If a last empty row of the input range is being edited (add value to an empty row), then insert an empty row, automatically.

I have been working on this problem for a few days but no solution. I can only do: Worksheet_change event to detect if there data within the defined name range is edited or selected, then insert a row. Even if I edit non-empty row (like the first row of the range), it still adds another row.

Please help!

=========================

I just think about the problem and probably I can do: if there is no empty row in range("Input_range"), then insert an empty row. I have not though about this. Well, still need to learn how to detect if a row of a range is empty but it is better. I will work on this and if I have problem I please help me.

It's hard to tell what your problem is from the description. Can you post the code that is giving you trouble? — Jon Crowell, May 15 '12 at 02:12
As a thought, especially if you are using XL 2007 or 2010, you might consider using a Table ("List" in XL 2003) instead. they have the functionality of adding a new row with formulas when you fill in the last row, and also have automatic totals, headers and filtering. http://www.jkp-ads.com/Articles/Excel2007Tables.asp — Doug Glancy, May 15 '12 at 03:14
Thank you. Here is the code http://i49.tinypic.com/2zqc775.jpg I know it is incomplete as I cannot figure out which is the syntax to detect a cell has been edited in in the last row. In other word if there is an no empty row in range "input" then insert an extra empty row — NCC, May 15 '12 at 03:26

score 2 · Accepted Answer · answered May 15 '12 at 08:46

Is this what you are trying?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MRange As Range, rng As Range

    On Error GoTo Whoa

    Set MRange = Range("InputRange")

    '~~> Get the last Row of the range
    Set rng = MRange.Range(MRange.Cells(MRange.Rows.Count, 1), _
    MRange.Cells(MRange.Rows.Count, MRange.Columns.Count))

    '~~> Trap changes in the last row of the range
    If Not Intersect(Target, rng) Is Nothing Then
        'If Application.WorksheetFunction.CountA(rng) > 0 Then
            'Application.EnableEvents = False

            'rng.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        'End If
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

thank you very much yes that is what I am looking for. Something like gridview — NCC, May 15 '12 at 16:22

How to detect if the cell being edited belongs to the last row of a defined name range

1 Answers1

Linked