Debra documents this on that link:

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.



JMay wrote:

Otto:
After running this macro I found that you must Close the file (and answer
"Yes" to the q Do you wish to save?) and then re-open to have the
Control-end actually goto the last-used cell. Could the Close/re-open be
included in the original macro?
TIA,

"Otto Moehrbach" wrote in message
...
You need to search for something in a cell, not a blank cell ("*"). Here
is
a macro from Debra Dalgliesh that resets the last cell of the sheet, rows
and columns both. HTH Otto
'This code is from Debra Dalgliesh at:
'http://www.contextures.on.ca/xlfaqApp.html#Unused
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



"Tempy" wrote in message
...
Could somebody help me with some code to perform this.

I have a spread sheet with a varying amount of rows.

It gets updated daily and i run some code to do various things. I need
to go down to the last row with data in and then delete the rest. I
think that when the spreed sheet is set up (by somebody else), they set
the area as when i use some other code that i found to go to the bottom
of data it finds more than what there is?!
The code below is what i use to see how many rows.

myRows = last
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox last

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



--

Dave Peterson