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 |
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 |
All times are GMT +1. The time now is 11:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com