Playing Peekaboo with the Used Range

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 Comments

  1. Oli says:

    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…

  2. Daniel says:

    Thank you, I have been struggling with this for a long time

  3. snb says:

    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. Jeff Weir says:

    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. Marostega says:

    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. gruff999 says:

    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. Jeff Weir says:

    @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.

  8. Hui says:

    @Oli

    EHA is recommencing on July 15, 2014.

    Hui…

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: