Excel resetting “UsedRange”

Question

Don't know what I'm missing, but the examples I see posted do not appear to work.

I import data from a web query. I set the query to clear unused cells when it re-queries.

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.

ActiveWorkbook.Save

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?

Answer 1

This may or may not suit your data needs, but if your data is all in one contiguous block, you can use CurrentRegion instead of UsedRange, like this:

With Cells(1, 1).CurrentRegion
 MsgBox "I have " & .Rows.Count & " rows and " & .Columns.Count & " columns of data."
End With

Of course, if the region you care about does not start at cell A1, or if your sheet contains multiple contiguous regions that you care about, this option will not work. Depending on how predictable your data is, you can usually find at least one cell in each block of data, and once you have that, CurrentRegion will give you the range of the entire block.

Answer 2

1. select cell 1,1 in any sheets you want to reset the UsedRange property
2. Calculate all worksheets in all open workbooks, regardless of whether they changed since last calculation (To Calculate Fully Ctrl+Alt+F9)
3. Save the workbook

Works for me on all versions of excel

Answer 3

I only needed to use Worksheets("Sheet1").UsedRange.Calculate after deleting rows to reset the range.

Answer 4

I double checked to make sure all the latests patches and service packs have been installed and they were.

I'm running Windows 10 and Excel 2016 version 16.0.6568.2034

I found that the range would only reset with the

ActiveSheet.UsedRange.Clear

And most importantly

ActiveWorkbook.Save

without the save command the range is not reset

Answer 5

I've used Jeeped solution and worked for me when i add .Activate, so:

With Worksheets("Sheet1")
        Debug.Print .UsedRange.Address(0, 0)
        .UsedRange.Clear
        .UsedRange   
        .Activate
        Debug.Print .UsedRange.Address(0, 0)
  End With

I'm using Excel2013

Answer 6

If you call the Worksheet.UsedRange property by itself, it will reset.

    With Worksheets("Sheet1")
        Debug.Print .UsedRange.Address(0, 0)
        .UsedRange.Clear
        .UsedRange    '<~~ called by itself will reset it
        Debug.Print .UsedRange.Address(0, 0)
    End With

This extra step is unnecessary in xl2010 and above with all appropriate service packs installed.