View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jim May Jim May is offline
external usenet poster
 
Posts: 477
Default Sudden Increase in File Size

A client gave me a workbook that according to them was 28MB but has recetly
gone up to 103MB, without any major changes. I noticed that it is currently
shared.

I told them I'd try and get it back down to a more reasonable file size. On
each of the 60 sheets I had them in Column A - the last active Row enter
"LR" (without the quotes ALSO in ROW 1:1 - the Last active Column enter "LC".

I removed the Share; I set the Calculation Method to Manual and I ran this
following Macro: But File is still 100++ MBs.. Any thoughts??

Sub RidBloat()
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("LR", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("LC", 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