Reduce Excel file size

[JoeS01]

I have a file with lots of data. Each time I add a new chart, or pivot table, it balloons out, well beyond what is expected. It is now 350MB in size. Is there any way I can reduce this size?

I would welcome all suggestions

 

[DominicB]

Good morning JoeS01

Are your some (or all) pivot tables based on the same range? If so, you can use (say) PT1 to summarise the range A1:M1000, and rather than base PT2 on the same range (which is memory inefficient) you can base it on PT1 - that's if you want to work on the same range. Excel will give you this option when you set up PT2.

Alternatively, do any of the points in this thread help?

http://www.mrexcel.com/board2/viewtopic.php?t=232835

HTH

DominicB

 

[JoeS01]

Thanks for your helpful reponse, Dom.

I tried saving the file as a .html web page, then restoring it as a .xls workbook, but the file size of the restored size was the same as the original

 

[Krishnakumar]

Also check out this

 

[mrhartley]

If it helps, yo might need to remove some formula, if you have it.
Copy / Paste Special Values.

This can help me sometimes.

Also the following code might help, it agains has helped on a rare occasion. Be sure to make a backup first.

Sub WorkbookReducer()
    'From: http://www.contextures.com/xlfaqApp.html#Unused
    ' Re-set used range
    
    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("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByRows).Row
                    myLastCol = _
                    .Cells.Find("*", 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

HTH
~Mark