Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Named ranges don't expand when adding rows/columns (Excel 2000 SR-1)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named ranges don't expand when adding rows/columns (Excel 2000 SR-1)

    Let me start off be a huge warning.

    Names that have no sheet reference in them sound nice, but Excel has a nasty bug that may give wrong results with this kind of names.
    As long as calculation is caused by Excel, these names indeed refer to the sheet the name is used in.

    But as soon as VBA invokes a calculation, those names will ALL refer to the active sheet!!!!!!!!!!!!!!!!!!!

    Therefor I STRONGLY advise you to start using a different method for these names alltogether.

    I'd suggest using INDIRECT instead.

    Second advice:

    Download my name manager from my site below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named ranges don't expand when adding rows/columns (Excel 2000 SR-1)

    Thank you for the warning about names -- I will certainly try to avoid using them in the future. They've been a pain to work with (and weren't my idea in the first place -- I didn't set up this sheet, but it's become my job to maintain it), so I'm glad to know there's an alternative. I'm not familiar with the INDIRECT function yet; I've looked it up in Excel's Help, but I don't see how to add it to a name yet in order to make the name always refer to the current sheet. Could you give me a quick example? Does it depend on the workbook you use it in?

    I have downloaded your name manager and will give it a look later today, when other work permits. Thank you very much for your help!

    Cris

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named ranges don't expand when adding rows/columns (Excel 2000 SR-1)

    IN its simplest form, you can use

    =INDIRECT("A1")

    in a name to refer to cell A1 on the sheet the name is used in.

    But this formula will not change when rows are inserted, it always refers to that range (which can be a benefit too).

    One way to get a formula that will adjust to row insertion (but only when they are inserted on the sheet the name was defined on!!):

    =INDIRECT(ADDRESS(ROW(Sheet3!$A$2),COLUMN(Sheet3!$ A$2)) & ":" & ADDRESS(ROW(Sheet3!$D$5),COLUMN(Sheet3!$D$5)))

    (refers to cells A25 of the sheet the name is used on)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •