Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
salary defined by slab table | Excel Worksheet Functions | |||
Pivot tables missing fields from defined table | Excel Discussion (Misc queries) | |||
Updating cells which reference a defined name | Excel Discussion (Misc queries) | |||
Updating a table...can this be done? | Excel Discussion (Misc queries) | |||
Copy data from excel to a defined table in another spreadsheet | Excel Programming |