Troubleshooting: Massive Increase in File Size (ADVANCED)

[Jax11]

Dear all,

I am a consultant and expert Excel user but have come across a problem I have not been able to solve.

PROBLEM

I have built a financial model, .xlsm, using Excel 2010 and Win7.

Originally the file was 1.6MB and was working extremely smoothly.

My client attempted to make some changes to a few lines of calculations, and somehow the file has shot up from its original 1.6MB to 36MB without apparent explanation.

FILE DETAILS

The file design involves:

- 15 "assumptions" worksheets for inputs (raw value inputs only, virtually no calculations)
- 1 "calculations" worksheet (2500 lines long, 20 columns wide, heavy calculations)
- 8 "reports" worksheets (virtually no calculations, only linking to calculations sheet).
- There are no macros or other VBA code
- There are no data tables
- I have used some named ranges but none are linked to other workbooks or giving errors
- There are no (apparent) links to external files

ATTEMPTED FIXES

I have done a copy / past values on every cell in the workbook and I'm still at 10MB.

Changing file type from .xlsm to .xlsx made no change
Changing file type from .xlsx to .xls dropped file size to 2.5MB, but still larger than the original 1.6MB and probably due to loss of formatting and 2010 features such as sparklines

HUNCH

Is it possible that Excel has an inbuilt RAM function or somehow "carries" linked or source files that may increase the .xlsm file size substantially?

Is the increase in size due to some background looping (due to poor formula design) that is invisible to the standard user interface?

I am attempting to narrow down and isolate individual lines of calculations that may be the problem, but am concerned something is happening invisibly in the background.

HELP

Any suggestions?? This one is a stumper!

Thanks in advance,
Jax11

 

[Bunglebogs]

I have done a copy / past values on every cell in the workbook and I'm still at 10MB.

Have you done this in a controlled manner (i.e. selected specific ranges) or just a Select-All. There may be formats or formulas applied to a greatly extended range that doesn't necessarily impact the output, but could increase the file size considerably.

When suffering similar woes previously, I have resorted to copying formulas (in a controlled manner) to a completely new workbook to overcome the issues.

 

[Sektor]

1. F5
2. Special...
3. Objects
4. Delete.

 

[Sektor]

Also try to reset last cell:

Sub DeleteUnusedFormats()
    
    Dim lLastRow As Long, lLastColumn As Long
    Dim lRealLastRow As Long, lRealLastColumn As Long
    
    With Range("A1").SpecialCells(xlCellTypeLastCell)
        lLastRow = .Row
        lLastColumn = .Column
    End With
    
    ' Get last cell with data.
    lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
    
    ' Delete extra rows and columnsm, if any.
    If lRealLastRow < lLastRow Then
        Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
    End If
    
    If lRealLastColumn < lLastColumn Then
        Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)).EntireColumn.Delete
    End If
    
    ' Reset last cell.
    ActiveSheet.UsedRange
    
End Sub