JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,532
- Office Version
- 365
- Platform
- Windows
I have a table of data to which I periodically add rows and/or columns. I have several expressions that refer to the entire range, such as:
If I add a row in the middle, it still works. The range in the calls gets expanded. But if I add one before the top row or after the bottom row, the range is not expanded and the new row is not included in the calculations.
My next approach was to name the cells just above the upper left corner and just below the lower right corner. Let's say I gave C5 the name "RangeBeg" and F12 the name "RangeEnd". Now I can use this call:
and I can add rows anywhere and the range will be correct.
Another approach that simplifies the expressions is to name the entire range. If I assign the name RangeAll to C6:F11, then my calls can look like this:
This is elegant, but it has the same problem as the first approach. Rows that are added at the ends will not expand the range.
What if I create a new named range that includes a one-cell border all the way around the body of the table (RangeAllPlus = B5:G13)? Then I can use the last 2 parameters to the Offset function to get expressions that dynamically extend no matter a row or column is added:
This works, but it is ugly. Is there a better, or at least prettier, way?
VBA Code:
=Sum(C6:F11)
=MyFn(C6:F11)
My next approach was to name the cells just above the upper left corner and just below the lower right corner. Let's say I gave C5 the name "RangeBeg" and F12 the name "RangeEnd". Now I can use this call:
VBA Code:
=Sum(offset(RangeBeg,1,0):offset(RangeEnd,-1,0))
=MyFn(offset(RangeBeg,1,0):offset(RangeEnd,-1,0))
Another approach that simplifies the expressions is to name the entire range. If I assign the name RangeAll to C6:F11, then my calls can look like this:
VBA Code:
=Sum(RangeAll)
=MyFn(RangeAll)
What if I create a new named range that includes a one-cell border all the way around the body of the table (RangeAllPlus = B5:G13)? Then I can use the last 2 parameters to the Offset function to get expressions that dynamically extend no matter a row or column is added:
VBA Code:
=SUM(OFFSET(RangeAllPlus,1,1,ROWS(RangeAllPlus)-2,COLUMNS(RangeAllPlus)-2))