named range changed when I inserted a row


Well-known Member
Mar 27, 2016

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


Well-known Member
Dec 23, 2017
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 :
then if you insert a row e.g. at row 3 you will find the equation has changed to:
Last edited:


MrExcel MVP, Junior Admin
Aug 1, 2002
Office Version
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

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...