Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Spreadsheet1.ActiveSheet.Range("B1").LoadText debugging | Charts and Charting in Excel | |||
Resetting the end of a worksheet | New Users to Excel | |||
Resetting the activesheet used range | Excel Programming | |||
Used Range is not resetting | Excel Programming | |||
Resetting cells | Excel Programming |