I need to make edits to one of my company’s standard spreadsheets, involving adding rows and columns to several sheets and changing or adding named ranges to include the new rows/columns. The sheet already has 300+ named ranges, and many of them use the format “=!$A$1” so that the range will always refer to the active sheet. Five of the sheets in the workbook are essentially identical, and this makes it simpler for macros to manipulate data in any of those sheets.
I know that ordinarily rows or columns added in the middle of a named range cause the range to expand, but none of my pseudo-global ranges seem to. If I have a range defined as “=!$A$1:$Z$1”, and add columns between A and Z, the range stubbornly ends at column Z. I can change the name definition from Insert | Name | Define, but this is time-consuming — especially since I can’t seem to use arrow keys to help change the name definition (as soon as I touch an arrow key, the dialog box thinks I want to add the name of the currently active cell to the definition, which is NOT what I’m looking for).
Most frustrating of all, I can’t seem to change these ranges via code, either. I can usually change a range with something like:
Names(“RangeName”).RefersTo = “new range definition”
but I can’t get it to accept a formula that doesn’t include a sheet name in front of the “!” Instead it gives me a run-time error 1004 “The formula you entered contains an error. Try one of the following…”
Considering how many of the existing ranges I need to change (probably most of them!), plus adding more in for the new columns/rows, I’d like to find an easier way than fixing them all manually; a macro would be great, but if I can’t get around the run-time error then there’s not much hope of that. Any suggestions are very welcome — I hope I’ve explained adequately, I’m still pretty new to Excel (I’ve taught myself a fair bit, but I’ve still got a long way to go!) Thanks very much!
Cris
