Instead of that try this. I had the same problem with imported sheets being
huge with blank space, this fixed them.

Sub Reset_all_lastcells()
'2002-08-02 based, David McRitchie, programming
' http://www.mvps.org/dmcritchie/excel...eanUpLastCells
'This macro will attempt to reset internals, based on a little trick
'involving usedrange.rows.count which may or may not work
'but would be nondestructive.
Application.Calculation = xlCalculationManual
Dim xlong As Long, cSht As Long
For cSht = 1 To ActiveWorkbook.Worksheets.Count
Worksheets(cSht).Select
xlong = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Columns.Count 'Tip73
Next cSht
ActiveWorkbook.Save
AbortCode:
'-- one of these is only done in macros make sure you exit thru here...
Application.Calculation = xlCalculationAutomatic

End Sub

Mike F

"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!