Shift the Left Boundary of Named Ranges when Inserting Columns

  • Thread starter Thread starter 6afraidbecause789
  • Start date Start date
6

6afraidbecause789

Hi Programmers--When columns are inserted to the left of ranges, the
ranges are not updating and shifting to the right. After browsing and
studying, I defined the ranges globally, but I'm not sure how to
adjust them to always be positioned as follows: below row 12, which
has cells that = date (11/10/08), and to the right of a column with
cells that have the text "Scores >>" in each cell.
Here are the current ranges:

Range1=!AV$13:!EZ$13,!AV$15:!EZ$15,!AV$17:!EZ$17,!AV$19:!EZ$19,!AV$21:!
EZ$21,!AV$23:!EZ$23,!AV$25:!EZ$25,!AV$27:!EZ$27,!AV$29:!EZ$29,!AV$31:!
EZ$31

Range2=!AV$31:!EZ$31,!AV$33:!EZ$33,!AV$35:!EZ$35,!AV$37:!EZ$37,!AV$39:!
EZ$39,!AV$41:!EZ$41,!AV$43:!EZ$43,!AV$45:!EZ$45,!AV$47:!EZ$47,!AV$49:!
EZ$49

Range3=!AV$51:!EZ$51,!AV$53:!EZ$53,!AV$55:!EZ$55,!AV$57:!EZ$57,!AV$59:!
EZ$59,!AV$61:!EZ$61,!AV$63:!EZ$63,!AV$65:!EZ$65,!AV$67:!EZ$67,!AV$69:!
EZ$69

There are 3 ranges instead of 1 because of Excel's 240 character
limitation in defining named ranges.

Thank you very much if you can help.
 
It looks like you need to make your columns static, try putting another $
before the column number like this
=Sheet1!$B$4:$B$21
 
The default action of named ranges, w/out the relative $, is to shift when
rows or columns re inserted or deleted. You can't avoid that. Or rather not
without using a dynamic style named range or a named formula with Indirect
or perhaps one or two other ways. But I doubt any such method will be
practical with your multi-area named ranges. (Relative named ranges move
according to the cursor position).

At a glance looks like its very easy to recreate your ranges in code (for
use in immediately in the code or to recreate the named ranges). At a glance
looks like they are alternate rows.

Regards,
Peter T
 
Back
Top