Excel workbook size quite large

Question

I have a workbook that is quite large (approx. 30 MB). I realize this is fairly small compared to some but the math just doesn't seem to add up.

• There are 19 sheets total and I have about 15-20 named ranges.

• All but four of these sheets have a small used range (under 100 rows and 25 columns)

• Two of the four are tables (minimal formatting) approx. 35,000 rows by 40 columns comprising of data paste-valued from separate files totaling approx. 20 MB.

• The other sheets are not tables and not formatted and approx. 9,000 rows 100 columns and 11,000 rows by 5 columns.

So my question is this: Where is the additional 10 MB coming from?

Edit:

Please note that I have already cut my used range down on each sheet to the bare minimum. Also, the workbook is copied from our company template with significant VBA but that file is less than 1 MB.

Answer 1

This problem usually occour when you copy data from different files into one because Excel don't manage correctly the last rows and the last columns numbers so use lots of data to store values of empty cells.

Try the following.

1st try

Use the following Excel Diet VBA script to reset the max col and row numbers.

Attribute VB_Name = "Module1"
Option Explicit

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), .Cells(.Rows.Count, .Columns.Count)).EntireColumn.Delete
            .Range("A" & LastRow + 1 & ":A" & .Rows.Count).EntireRow.Delete
        End With
    Next

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

2nd try

Copy all of your data to a brand new file and save.