excel file huge

[nikko50]

I have an excel file that is 50mb and I don't know why it is that huge. I did not create this file and was asked to look at it and figure out why it takes 10 minutes to open? There is 3 tabs and very little data. How can it be so huge??

 

[TheBuGz]

Dear just_jon,

An excellent piece of Code. I have used the code and it works fine too in this post courtsey of Fire Fytr bro

http://www.mrexcel.com/board2/viewtopic.php?t=128550&highlight=

Can you please change your code for the results i want.

just_jon i know you are one of the BEST person and MVP as well

 

[Alkemist]

That macro works very well...Is there any way the code can be changed to do the same job on a file without having to open the file.

The problem I've been having is that some files I have are so huge they take ages to open. If I can clear them out wihtout opening the file it would provide a perfect solution.

Cheers...Alkemist

 

[sopranoiam]

PLEASE SOMEONE TELL ME....how do you "run" that long code above? Do you copy it into a macro form? I have the same problem and would like to fix it.

 

[sopranoiam]

PLEASE SOMEONE TELL ME....how do you "run" that long code above? Do you copy it into a macro form? I have the same problem and would like to fix it.

 

[Norie]

sopranoiam

What code are you referring to?

 

[justme]

hi there, just tried this code and I did lose charts on two sheets but not others. Don't know why. Check all your sheets before saving!

 

[just_jon]

hi there, just tried this code and I did lose charts on two sheets but not others. Don't know why. Check all your sheets before saving!

Try

Sub ExcelDiet() 
     
    Dim j               As Long 
    Dim k               As Long 
    Dim LastRow         As Long 
    Dim LastCol         As Long 
    Dim ColFormula      As Range 
    Dim RowFormula      As Range 
    Dim ColValue        As Range 
    Dim RowValue        As Range 
    Dim Shp             As Shape 
    Dim ws              As Worksheet 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
    On Error Resume Next 
     
    For Each ws In Worksheets 
        With ws 
             'Find the last used cell with a formula and value
             'Search by Columns and Rows
            On Error Resume Next 
            Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) 
            Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _ 
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) 
            Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
            Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
            On Error Goto 0 
             
             'Determine the last column
            If ColFormula Is Nothing Then 
                LastCol = 0 
            Else 
                LastCol = ColFormula.Column 
            End If 
            If Not ColValue Is Nothing Then 
                LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column) 
            End If 
             
             'Determine the last row
            If RowFormula Is Nothing Then 
                LastRow = 0 
            Else 
                LastRow = RowFormula.Row 
            End If 
            If Not RowValue Is Nothing Then 
                LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row) 
            End If 
             
             'Determine if any shapes are beyond the last row and last column
            For Each Shp In .Shapes 
                j = 0 
                k = 0 
                On Error Resume Next 
                j = Shp.TopLeftCell.Row 
                k = Shp.TopLeftCell.Column 
                On Error Goto 0 
                If j > 0 And k > 0 Then 
                    Do Until .Cells(j, k).Top > Shp.Top + Shp.Height 
                        j = j + 1 
                    Loop 
                    If j > LastRow Then 
                        LastRow = j 
                    End If 
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width 
                        k = k + 1 
                    Loop 
                    If k > LastCol Then 
                        LastCol = k 
                    End If 
                End If 
            Next 
             
            .Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete 
            .Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete 
        End With 
    Next 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
     
End Sub

Also courtesy DRJ...

 

[justme]

good morning Jon.

Thanks for the code. I'll put it in a safe place. Unfortunately, I can't test it on the file I tried it on. I saved before checking out all the sheets....
Hindsight is 20/20!

Hope you have a great week.

 

[RAM]

I believe I could have some issues too with huge size of the Excel book and old references to Ranges.

I have "everything" of Excel functions in my book, so it kind of is excused to be big in size, but still too big and too slow, but it never crashes.

I run Jon's codes but got a run time error in one of my latest codes. I exit the error from VBA, closed VBA and tried to save to see the new size, but I got an error "File not saved" (never seen that msg before).

Jon: Do you have another way of deleting old invalid references of ranges?

RAM

 

[Fazza]

Something else that I recently encountered. Via VBA a few worksheets were moved to a new workbook (output report). The code had used some defined names and these were located in the worksheets that were moved. The names related to array variables used in the VBA and were for large ranges - say 20,000 rows x 30 columns. The originaly workbook retained a reference to the moved defined names and this caused a massive file bloat. It was remedied by adding a few more lines at the end of the original VBA. Such as Names("whatever").Delete This dropped the file size down from tens of MB to less than 1. Regards, Fazza