Results 1 to 3 of 3

Thread: named range changed when I inserted a row

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    1,439
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default named range changed when I inserted a row

    Hi

    I named A1:A10 as myrange1. Then I inserted a empty row between row 2 and 3. To my surprised now myrange1 is actually A1:A11 ?

    Is that by design? if yes, then what is the point of naming range when I can not grante, it will point to the cells I want. Thank you

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    928
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: named range changed when I inserted a row

    It is by design and it is very useful because of it, this is because it allows you to add data to a worksheet and all the equations work without needing to change them all. This behaviour happens whether it is a named range or not. If you have an equation such as :
    =Sum(A1:A10)
    then if you insert a row e.g. at row 3 you will find the equation has changed to:
    =Sum(A1:A11)
    Last edited by offthelip; Sep 25th, 2019 at 12:02 PM.
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: named range changed when I inserted a row

    Using named ranges often makes things like formula easier to work with, especially if you are copying, autofilling, or dragging the formulas, and don't want the range references to change as you do that (I use them mostly when I am doing VLOOKUP and returning multiple columns, so have multiple formulas that I want to copy).

    However, just like any other range reference, if you insert or delete a row within that range, the range will automatically grow/shrink to reflect. In most cases, this is what you want to happen.
    I cannot think of too many instances where I have a named range, and I adjust the number of rows contained within that range, and not want my named range to reflect that change. Most of the time, it would cause issues for me if that range size did NOT change.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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