One of the warnings on Debra's site:

4. Save the file. Note: In older versions of Excel, you may have to Save, then
close and re-open the file before the used range is reset.

What version of excel are you using and did you save, close and reopen?



davidm wrote:

Thanks for all the replies. Having gone through the various variants of
applying the Usedrange property to reset the last cell, am I right to
conclude that the method is not 100% fail-safe?

The following code (from
http://www.contextures.com/xlfaqApp.html#Unused) raises that question.
Take a fresh Worksheet, and enter a figure in Range($H$58000). Clear the
cell and run the code. Result? The scroll bar stays down at
$H$58000!

To programatically reset the used range,

Note: This code may not work correctly if the worksheet contains merged
cells. To check your worksheet, you can run the TestForMergedCells code.



Sub DeleteUnused()


Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks

End Sub

--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=473521

--

Dave Peterson