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:
- Hide that pesky column again.
- Unhide that pesky column again, using the Right Click > Unhide method.
- Delete that pesky column.
- 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:
…then the row is unhidden.
But if you hide a column, and then double click when this comes up:
…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.
