LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
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





 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
File size increase Tania Excel Discussion (Misc queries) 1 January 31st 07 08:26 AM
File size increase Don4908 Excel Discussion (Misc queries) 6 December 22nd 06 08:10 AM
File Increase in size Martinj Excel Discussion (Misc queries) 7 November 7th 05 02:00 PM
Excel file size - sudden increase Bradley Excel Discussion (Misc queries) 0 September 23rd 05 07:47 AM
File size increase Bill Clark Excel Discussion (Misc queries) 1 January 28th 05 06:57 PM


All times are GMT +1. The time now is 11:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"