Excel resetting "UsedRange"
<p>Don't know what I'm missing, but the examples I see posted do not appear to work.</p>

<p>I import data from a web query.  I set the query to clear unused cells when it re-queries.
<a href="https://i.stack.imgur.com/ZLLwl.gif" rel="noreferrer"><img src="https://i.stack.imgur.com/ZLLwl.gif" alt="As shown here in the last radio button"></a></p>

<p>I used this imported data to generate a report of variable length.</p>

<p>However if the user (as they need to do in my case) insert rows then the ActiveSheet.UsedRange is expanded.  This means I cannot any longer just do a "Ctrl-End" to find the last row in the data set when a new query is performed.</p>

<p>I can easily clear any data with ActiveSheet.UsedRange.Clear.   However if the previous query generated a 2 or 3 page report any subsequent query will also be that long even when there is less data because the "UsedRange" still points to that last row way down there.</p>

<p>The examples shown like</p>

<pre><code>ActiveSheet.UsedRange
ActiveSheet.UsedRange.Clear
a = ActiveSheet.UsedRange.Rows.Count
</code></pre>

<p>do not reset the range.</p>

<p>MS defines UsedRange as a readOnly property.</p>

<p>It appears what needs to happen is a "File Save" in order to complete the action.</p>

<pre><code>ActiveWorkbook.Save
</code></pre>

<p>One post noted that in older versions of Excel you also had to close the workbook and reopen it to complete the action.</p>

<p>I would like to know
1. What is the version cutoff where this behavior changed?
2. Is there some other method using a VBA macro which will reset the range?</p>