named range changed when I inserted a row

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,492
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
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
955
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,078,467
Messages
5,340,500
Members
399,379
Latest member
Ashrafkamal

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top