dynaically updating a defined table name address
 

I have a table name "tblData" which for the sake of reference refers to
$B8:$R418, as well as a cell right below it with a defined name of
"ptrInsertRow"

The authors of a book where I got these ideas like to have ptrInsertCell at
the bottom of the table, and insert there. This means there is a gap over
it, assuming the table is filled with data (which it should be). The authors
than use code to copy the last row to the newly inserted row above it,
before clearing the contents of the last row. The table stays dynamic.

It's fine enough, but I have code that depends on a copied row being in the
clipboard getting inserted for free. I leave the ptrInsertRow right below
the table, so my table does not stay dynamic from the insert. I could work
out the copy mechanics I suppose, but since I always know how many rows are
added, it would seem just as easy to update the refers to of the tblData. So
if I add one row, the address just needs to be what it was plus 1, or
$B8:$R419.

Anyone know some quick code that would do what I want?

Thanks, Eric

dynaically updating a defined table name address
 

why not just use dynamic range names?

Anyway, the following code will increment the rable by one row. Its pretty
clear, so you should be able to adapt it quite easily.

Sub IncrementTable()
Dim Source As Range
Const TABLENAME As String = "tblData"
Set Source = ThisWorkbook.Names.Item(TABLENAME).RefersToRange
With Source
With .Resize(.Rows.Count + 1)
.Name = TABLENAME
.Interior.ColorIndex = 34
End With
End With

End Sub


"Eric" wrote:

I have a table name "tblData" which for the sake of reference refers to
$B8:$R418, as well as a cell right below it with a defined name of
"ptrInsertRow"

The authors of a book where I got these ideas like to have ptrInsertCell at
the bottom of the table, and insert there. This means there is a gap over
it, assuming the table is filled with data (which it should be). The authors
than use code to copy the last row to the newly inserted row above it,
before clearing the contents of the last row. The table stays dynamic.

It's fine enough, but I have code that depends on a copied row being in the
clipboard getting inserted for free. I leave the ptrInsertRow right below
the table, so my table does not stay dynamic from the insert. I could work
out the copy mechanics I suppose, but since I always know how many rows are
added, it would seem just as easy to update the refers to of the tblData. So
if I add one row, the address just needs to be what it was plus 1, or
$B8:$R419.

Anyone know some quick code that would do what I want?

Thanks, Eric