<div>
Better use a table IMO, as it auto expand.<br>It will be less troublesome.<br><br>With Excel, whenever you can do something without VBA, do so. It will be much easyer to maintain.
</div>


<div>
Fabrice is right about using a table, but if there's some reason you don't want to, the Refers to formula in your named range can be modified to be dynamic. Does the named range have a heading?
</div>


<div>
This Refers To assumes there's a one-row heading<br />
<br />
=OFFSET(Sheet1!$A$2,0,0,CO<wbr />UNTA(Sheet<wbr />1!$A:$A)-1<wbr />,1)<br />
<br />
This one assumes there's no heading.<br />
<br />
=OFFSET(Sheet1!$A$1,0,0,CO<wbr />UNTA(Sheet<wbr />1!$A:$A),1<wbr />)
</div>


<div>
I am trying to past additional data to existing data and have it include that data in the range.<br />
Even when I use a table - if I paste data in at the end of the table, it does not extend the table.<br />
<br />
Looks like either way, I'm going to have to add the data and use vba to create a new range.
</div>


<div>
Did yo try changing the existing Named Range's Refers To formula to one of my examples.? You would have to possibly change the sheet name and column letter.
</div>


<div>
So the solution seems to be to remove and redefine named ranges each time new data is brought in.<br />
<br />
I figured out how to delete the named range<br />
<br />
This should create the new named range in the 'ur data' but I am getting an error on <br />
&nbsp; sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select<br />
<br />
Can you help?<br />
<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-43286717-1"><span>Sub DynamicRange()</span>
<span></span>
<span>Dim sht As Worksheet</span>
<span>Dim LastRow As Long</span>
<span>Dim LastColumn As Long</span>
<span>Dim StartCell As Range</span>
<span>Dim Rng As Range</span>
<span></span>
<span>Set sht = Worksheets(&quot;ur data&quot;)</span>
<span>Set StartCell = Range(&quot;A1&quot;)</span>
<span></span>
<span>'Refresh UsedRange</span>
<span>  Worksheets(&quot;ur data&quot;).UsedRange</span>
<span></span>
<span>'Find Last Row and Column</span>
<span>  LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row</span>
<span>  LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column</span>
<span></span>
<span>'Select Range</span>
<span>  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select</span>
<span>  Set Rng = Cells.Select</span>
<span></span>
<span></span>
<span>'Name Range</span>
<span>ThisWorkbook.Names.Add Name = &quot;ur_data&quot;, RefersTo:=Rng</span>
<span></span>
<span>  </span>
<span>End Sub</span>
</code></pre>
</div>


<div>
You don't need to do that. Just use Formulas-&gt;Name Manager to change the Refers To formula, If you attach your workbook I can do it for you.
</div>


<div>
I'm sorry! I misunderstood what to do with the offset! <br />
I tried and only got the first row in the data.<br />
Can you help? I appreciate it!<br />
<a href="https://filedb.experts-exchange.com/incoming/2021/05_w20/1560734/range.xlsx" target="_blank" class="file-inline" title="dynamic named range (10 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>range.xlsx</a>
</div>


range.xlsx

<div>
Please see the attached workbook. Before adding the &quot;x&quot; in cell A11 I added the Named Range shown in the picture. The Refers To formula is <b>=OFFSET('ur data'!$A$2,0,0,COUNTA('ur data'!$A:$A)-1,1)</b> I then added the &quot;x&quot; in A11 and to prove that that cell was automatically included in the Name Range I went to Name Manager and clicked on the Refers To formula and as you can see, A2 to A11 was selected.<br />
<a href="https://filedb.experts-exchange.com/incoming/2021/05_w20/1560737/2021-05-11_06-59-49.png" target="_blank" title="2021-05-11_06-59-49.png (373 KB)"><img alt="2021-05-11_06-59-49.png" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2021/05_w20/800_1560737/2021-05-11_06-59-49.png" /></a><a href="https://filedb.experts-exchange.com/incoming/2021/05_w20/1560735/29215517.xlsx" target="_blank" class="file-inline" title="29215517.xlsx (9 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29215517.xlsx</a>
</div>


2021-05-11_06-59-49.png

29215517.xlsx

<div>
When I try to use this range in A:A, it will not work for a pivot or lookup because only column A is the range.<br />
Am I right about that?
</div>


<div>
Yes. What range do you want ur_data to include?
</div>


Euro5

<div>
Ok that's it! Let me try that - thank you!
</div>


<div>
You’re welcome and I’m glad I was able to help.<br />
<br />
If you expand the “Full Biography&quot; section of <a href="https://www.experts-exchange.com/M_258447.html" rel="ugc">my profile</a> you’ll find links to some articles I’ve written that may interest you.<br />
<br />
Marty - Microsoft MVP 2009 to 2017<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Experts Exchange Most Valuable Expert (MVE) 2015, 2017<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Experts Exchange Distinguished Expert in Excel 2018<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Experts Exchange Top Expert Visual Basic Classic 2012 to 2020<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Experts Exchange Top Expert VBA 2018 to 2020
</div>


<div>
<div class="content wysiwyg-content fr-view">
If I add rows to a named range, how can I change the scope using VBA?<br>If range is ur_data and I add 4 rows - can I change the scope? Or do I need to use a table?<br>Thanks!&nbsp;
</div>
</div>



If I add rows to a named range, how can I change the scope using VBA?
If range is ur_data and I add 4 rows - can I change the scope? Or do I need to use a table?
Thanks! 

VBA adding rows to named range

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.