Playing Peekaboo with the Used Range

Posted on by Jeff Weir

I was trying to reset the used range in a spreadsheet template I inherited the other day, that had a much bigger used range than it should have had. Damned if I could get it to reset. Tried deleting rows and columns below and to the right of the actual used range. Tried this:

Sub ResetUsedRange()
Dim sht As Worksheet
Dim lng As Long

For Each sht In ActiveWorkbook.Worksheets
lng = sht.UsedRange.Rows.Count
Next

End Sub

Still no joy.

Then after much playing around, I discovered the problem. Several problems, really:

  • If you hide a row – or adjust it’s width, then the UsedRange is extended down to that row. But only for rows. And deleting all the rows below your ‘actual’ used range soon fixes this problem.
  • Hiding columns or adjusting their width does nothing to the used range. Unless you hide a column, and then unhide it by manually dragging on the boundary of the hidden column in order to resize it. Then you just screwed up the UsedRange bigtime: the used range now extends to that column, and it is devilishly hard to reset it again. Deleting all columns to the right of your ‘actual’ used range does NOT fix the problem.

Try it. You’ll find that the extended used range this gives is much more persistent than the Spanish Football Team.

After much experimentation, I found that you can only reset the used range back to what it should be if you:

  1. Hide that pesky column again.
  2. Unhide that pesky column again, using the Right Click > Unhide method.
  3. Delete that pesky column.
  4. Delete the perfectly innocent column that took it’s place.

Then and only then could I get the used range to go back to it’s cage. Weird. But might explain why you’ve had used ranges in the past that you simply could not get to behave.

(UPDATE:As demonstrated by snb’s code, the weird result you get by resizing a hidden column disappears if you close and then reopen the workbook.)

In the course of all this, I noticed that if you hide a row, then if you put the cursor over the hidden row and double-click when this icon comes up:
Autofit Row
…then the row is unhidden.

But if you hide a column, and then double click when this comes up:
Autofit Column
…absolutely nothing happens. What’s with that?

In both cases, the Macro Recorder spits out Rows(“25:25”).EntireRow.AutoFit or Columns(“M:M”).EntireColumn.AutoFit as the case may be. But in the case of hidden columns, it just doesn’t seem to do anything.

Weird again.

8 thoughts on “Playing Peekaboo with the Used Range”

  1. I just knew with a title like that it had to be one of your posts :) On a side note, remember the old EHA – it’s still not complete…

  3. If I want to reduce the usedrange I use this macro (you know the effect will be apparent only after saving & reopening the file).

    Sub M_snb()
    For Each cl In Cells.SpecialCells(2)
        c00 = Application.Max(c00, cl.Row)
        c01 = Application.Max(c01, cl.Column)
    Next
    
    For Each cl In Cells.SpecialCells(-4123)
        c00 = Application.Max(c00, cl.Row)
        c01 = Application.Max(c01, cl.Column)
    Next
    
    For Each cl In Cells.SpecialCells(-4123)
        For Each it In cl.Precedents
            c00 = Application.Max(c00, it.Row)
            c01 = Application.Max(c01, it.Column)
        Next
    Next
    
    Rows(c00 + 1 & ":" & Rows.Count).Delete
    Columns(c01 + 1).Resize(, Columns.Count - c01).Delete
    ThisWorkbook.Close -1
End Sub

  4. Hi snb. Code errorred out for me until I deleted the bit re precedents. Because not every formula has one.

    That said, I see from running the code that the strange used-range extension caused by the manual resizing of a hidden column doesn’t persist after the workbook closes. Although if you don’t delete the rows, then the resizing of a row persists.

    So the learnings from this are:

    • If you’ve got an overly large range caused by a hidden or resized row, delete the row concerned.
    • If you’ve got an overly large range caused by a column that you manually resized from hidden, then simply close and reopen the workbook.

  5. Te reset the used range of the active sheet, I use this code after deleting all rows/columns unnused.
    Has´s been working perfectly. Maybe you want to try:

    Application.ActiveSheet.UsedRange

    Regards,

  6. I hid column G, resized it by dragging (UsedRange now $G$1), then deleted column E (UsedRange now $F1$G1).

    If I do the “hide G > unhide G > delete G > delete G” fix it works, but also if I “hide G > unhide G > delete G and H together”.

    If I hide then resize a row, then delete just that row the UsedRange resets to $A$1.

    p.s. England 0 – Uruguay 2.

  7. @Marostega: That is essentially the same approach as the code listing in my article. THe issue I was having was due to a resized column, and it turned out that neither your code snippet nor mine would fix the problem until the file was closed and reopened.

    @gruff999: Cheers…good to know. As per the update above, the weird result you get by resizing a hidden column disappears if you close and then reopen the workbook.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.