Don't know what I'm missing, but the examples I see posted do not appear to work.
I used this imported data to generate a report of variable length.
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.
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.
The examples shown like
ActiveSheet.UsedRange ActiveSheet.UsedRange.Clear a = ActiveSheet.UsedRange.Rows.Count
do not reset the range.
MS defines UsedRange as a readOnly property.
It appears what needs to happen is a "File Save" in order to complete the action.
One post noted that in older versions of Excel you also had to close the workbook and reopen it to complete the action.
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?