Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Ranges or References in Code when Adding or Deleting Rows
Greetings Programmers,
Would there happen be a way to update all ranges or references in modules when one or more rows are added or deleted? Reason I ask is that the 'heading' row on a sheet starts on row 12, and users may wish to add or remove rows above this heading line. Examples of code with set ranges a Set ActivityDate = ActiveSheet.Range("C11") and ActiveSheet.Cells(lastcolumn + 1).Offset(12, 0).Select ....so references such as C11 and (12,0) would be affected. Just curious. Thanks, Arnold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Ranges or References in Code when Adding or Deleting Rows
One way to do that would be to create named ranges (insert=Name=define) and
refer to those in your code rather than absolute cell addresses. The formula specifying the location of the named range will adjust like any other formula in excel and the name will continue to point to the same cell, even though it has moved. Another way is to calculate the location from some base location or use the find method to find a reference value and offset from there. -- Regards, Tom Ogilvy "Arnold" wrote: Greetings Programmers, Would there happen be a way to update all ranges or references in modules when one or more rows are added or deleted? Reason I ask is that the 'heading' row on a sheet starts on row 12, and users may wish to add or remove rows above this heading line. Examples of code with set ranges a Set ActivityDate = ActiveSheet.Range("C11") and ActiveSheet.Cells(lastcolumn + 1).Offset(12, 0).Select ....so references such as C11 and (12,0) would be affected. Just curious. Thanks, Arnold |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding and deleting rows with formulas ****Need Help**** | Excel Discussion (Misc queries) | |||
Updating, Deleting and inserting rows over two Excel Sheets | Excel Worksheet Functions | |||
Keeping a series table from updating when adding new rows. | Charts and Charting in Excel | |||
deleting mutliple rows in different ranges at same time | Excel Programming | |||
Updating (Adding Rows) To A Second Worksheet | Excel Programming |