Ways to reduce file size

[MrMajinbuu]

HI,

I have constantly run in errors like " Too many different cell formats", on top of that it takes long to open and save file. Current file size is 36MB.
Can anyone please advise which of the following actions take up large file size and/or slow down spreadsheet? and any recommended fix?

My workbook contains:

1. snippets within spreadsheet
2. vlookup and direct cell reference to data link to external workbook
3. sumifs data within same workbook (120,000 rows)
4. sumifs data on external workbook (120,000 rows)
5. various shape and difference color and font formatting
6. Hyperlink within same workbook

Thanks in advance

 

[Logit]

Create a COPY of your workbook.

Paste the following in your COPY and run it.

See what size the workbook is when the macro is done.

Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub

 

[Alex Blakenburg]

@Logit - That is very heavily dependant on Row 1 containing all your column headings and Column A containing data in the last used row.
If either of those 2 are blank it can delete the entire spreadsheet contents.

 

[Alex Blakenburg]

On the too many cell formats issue, can you show us a screen shot of Home > Cell Styles ?
Also do you have lots of conditional formatting ?

 

[MrMajinbuu]

On the too many cell formats issue, can you show us a screen shot of Home > Cell Styles ?
Also do you have lots of conditional formatting ?

as shown below.

The only way i can apply format is by copying a cell format and paste special - format

 

[Alex Blakenburg]

You are going to have to run a macro to remove all the custom styles. Have you used macros before ?

On a copy of your workbook. Goto the VB editor, insert a module and copy the code below into that module.
Then run it. If you have a lot of styles and I expect you do, it might take a few minutes.

Sub StyleKill()
' Deleting Unwanted Styles
    Dim styT As Style
    Dim intRet As Integer
    Dim intCnt As Long
    Dim totStylesCnt As Long
    Dim oldStatusBar As Boolean
    Dim strMsg As String
    Dim i As Long
       
    'store the status bar setting
    oldStatusBar = Application.DisplayStatusBar
    'display the status bar
    Application.DisplayStatusBar = True       
    
    If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub
    
    totStylesCnt = ActiveWorkbook.Styles.Count
    
    With ActiveWorkbook
        For i = totStylesCnt To 1 Step -1
            If Not .Styles(i).BuiltIn Then
                
                On Error Resume Next
                .Styles(i).Delete
                On Error GoTo 0
            
                intCnt = intCnt + 1
                
                If intCnt Mod 500 = 0 Then
                    'display a message
                    strMsg = "Total No of Styles " & totStylesCnt & " - Deleted " & intCnt & " Styles"
                    Application.StatusBar = strMsg
                End If
                
            End If
    
        Next i
    End With
    
    MsgBox "No of Styles Removed:- " & intCnt
       
    'remove any text in the status bar area
    Application.StatusBar = False
    'reset the status bar to the user's preference
    Application.DisplayStatusBar = oldStatusBar
    
End Sub

 

[Joe4]

Also note that the large size of your file(s) and slow performance is also an indication that you may be using the wrong tool for the project!
When I here of lots of data (over 100,000 rows), and lots of formulas like VLOOKUPS and SUMIFS, it sounds to me like what you really are working with is a relational databases (lots of inter-related data). As such, it is typically much better to use a tool made for working with relational databases, like Microsoft Access or SQL. That is what they are designed for.

Though Excel can do it via brute force, that is not what it really was designed for, and thus it is often cumbersome and performance can be less than ideal.
Note if you are unable to use a relational database program and are stuck working with Excel, you should look into using Power Query, which better allows you to perform relational database operations in Excel. There is a forum on this board called Power Tools for those tools.

 

[Logit]

Alex Blakenburg:​

Any concerns relating to the macro are minimized by executing it on a COPY of the workbook first. After the macro runs, review the workbook for overall size (and if it has been reduced) then
review the contents of the workbook for accuracy.

 

[MARK858]

If no autofilters active on the sheets (if you need to keep formulas returning "" past your data change xlValues to xlFormulas)

Sub LoseThatWeightx2()

    Dim x As Long, LastRow As Long, LastCol As Long, yy As Long

    Application.ScreenUpdating = False

    For x = 1 To Worksheets.Count
 
        With Worksheets(x)
            On Error Resume Next
            LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(rows.Count, columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(rows.Count, columns.Count)).Delete
            On Error GoTo 0
        End With
 
        yy = Application.Worksheets(x).UsedRange.rows.Count
 
    Next x
 
    Application.ScreenUpdating = True
End Sub

 

[MrMajinbuu]

You are going to have to run a macro to remove all the custom styles. Have you used macros before ?

On a copy of your workbook. Goto the VB editor, insert a module and copy the code below into that module.
Then run it. If you have a lot of styles and I expect you do, it might take a few minutes.

Sub StyleKill()
' Deleting Unwanted Styles
    Dim styT As Style
    Dim intRet As Integer
    Dim intCnt As Long
    Dim totStylesCnt As Long
    Dim oldStatusBar As Boolean
    Dim strMsg As String
    Dim i As Long
      
    'store the status bar setting
    oldStatusBar = Application.DisplayStatusBar
    'display the status bar
    Application.DisplayStatusBar = True      
   
    If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub
   
    totStylesCnt = ActiveWorkbook.Styles.Count
   
    With ActiveWorkbook
        For i = totStylesCnt To 1 Step -1
            If Not .Styles(i).BuiltIn Then
               
                On Error Resume Next
                .Styles(i).Delete
                On Error GoTo 0
           
                intCnt = intCnt + 1
               
                If intCnt Mod 500 = 0 Then
                    'display a message
                    strMsg = "Total No of Styles " & totStylesCnt & " - Deleted " & intCnt & " Styles"
                    Application.StatusBar = strMsg
                End If
               
            End If
   
        Next i
    End With
   
    MsgBox "No of Styles Removed:- " & intCnt
      
    'remove any text in the status bar area
    Application.StatusBar = False
    'reset the status bar to the user's preference
    Application.DisplayStatusBar = oldStatusBar
   
End Sub

Done that. Before 28,030KB, after 27,401 KB. Number of styles removed - 64,299. However, only gives 1 MB of size reduction. Is it normal?
I am able to apply format now without having the error message pop up "Too many different cell formats."

In the script provided, does it remove style format only? All I concern is whether it removes formulas, cell reference etc which may screw up my calculation and i haven't checked the workbook thoroughly

Thanks for the awesome fix btw