Resetting the activesheet used range
 

here is a routine from David McRitchie:

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
"Mark" wrote in message
...
Hi,

I am using EXCEL 97 at work, I have a large worksbook with about 200
worksheets in it.

The file size is quite large and having an impact on it's use because as
the
file is on a server some distance away, it's on a 1meg link but still
takes
some time to open.

I have tried reducing the fiule size with the CodeCleaner programme from
Rob
Bovey but that hasn't reduced it hardly any.

I wonder whether part of the problem is whether each spreadsheets used
range
is more than it needs to be.

Is there some code that someones got that can run and look at each
worksheet
throught the workbook and reset the usedrange?

Any assistance would be greatly appreciated.

Thanks

--
Mark A Whitehead