How to reduce excel file size

Posted on 2014-08-07
Last Modified: 2014-08-18
Hi All

           i have been searching how to reduce an excel file size, as in my company, some of the excel file has reached over 100mb due to many macro has been added in the excel file, and i found below in some forum, and any idea how do i do below ?

opy this code into your personal workbook, then run the resetallusedranges macro and see if that helps after you save the workbook:

Sub ResetAllUsedRanges()
   Dim wks As Worksheet
   For Each wks In ActiveWorkbook.Worksheets
      ResetUsedRange wks
   Next wks
End Sub
Sub ResetUsedRange(Optional wks As Worksheet)
   Dim lngLastRow As Long, lngLastCol As Long, lngRealLastRow As Long, lngRealLastCol As Long
   On Error Resume Next
   If wks Is Nothing Then Set wks = ActiveSheet
   lngLastRow = 1
   lngLastCol = 1
   With wks
      With .Range("A1").SpecialCells(xlCellTypeLastCell)
         lngLastRow = .Row
         lngLastCol = .Column
      End With
      lngRealLastRow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
      lngRealLastCol = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
      If lngRealLastRow < lngLastRow Then .Range(.Cells(lngRealLastRow + 1, 1), .Cells(lngLastRow, 1)).EntireRow.Delete
      If lngRealLastCol < lngLastCol Then .Range(.Cells(1, lngRealLastCol + 1), .Cells(1, lngLastCol)).EntireColumn.Delete
      Debug.Print .UsedRange.Count
   End With
End Sub
Question by:piaakit
    LVL 3

    Expert Comment

    by:Peter Haussl

    Marco's on its own are most likely not the problem itself. Reducing the filesize of a file (in general) is a topic on its own.
    First depends how much data you have inside. So might be that data in your file are really requiring that amount of data.

    Excel itself as its format is containing also lots of "dead" fregments in its format which appear when you are very often  changing data and make versioning or change logging.

    Some way to reduce xls size are described here

    Maybe one of those is moving you forward.

    LVL 27

    Expert Comment

    by:Glenn Ray
    Here are three macros that I run on occasion to reduce file size.  The first one, Delete_Unused_Rows_Columns, is very effective since extraneous blank rows and columns take up nearly as much memory as cells with values in them.
    Sub Delete_Unused_Rows_Columns()
        Dim intLastRow, intLastCol, r, c As Long
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        intLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
        Application.ScreenUpdating = False
        r = intLastRow
        Do Until Application.CountA(Rows(r)) > 0
            r = r - 1
        intLastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
        Application.ScreenUpdating = False
        c = intLastCol
        Do Until Application.CountA(Columns(c)) > 0
            c = c - 1
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        MsgBox "Removed Blank Columns and Rows."
    End Sub

    Open in new window

    These two have less impact, but do help clean up bad styles and range names.  The first is from Dave Peterson; I don't recall where I got the second one.
    Sub DelStyle()
        ' macro from Dave Peterson, Excel MVP
        'On Error Resume Next
        Dim myStyle As Style
        For Each myStyle In ActiveWorkbook.Styles
            If myStyle.BuiltIn Then
                'do nothing
            End If
        Next myStyle
        MsgBox "Done!", vbOKOnly, "Deleted Styles"
    End Sub

    Open in new window

    Sub Remove_Bad_Names()
        'Removes range names with invalid references (non-existent)
        Dim testRng As Range
        Dim myName As Name
        For Each myName In ActiveWorkbook.Names
            Set testRng = Nothing
            On Error Resume Next
            Set testRng = myName.RefersToRange
            On Error GoTo 0
            If testRng Is Nothing Then
                'doesn't refer to a real range
                'MsgBox myName.Name
            End If
        Next myName
    End Sub

    Open in new window

    LVL 31

    Accepted Solution

    To firstly check whether any of this is actually required, on each sheet press "Ctrl + End" or "End then Home" and the cursor should go to the bottom right of the expected data area.

    If it goes way beyond the expected area then the above may help.

    If the cursor goes to the bottom right of data as expected then maybe the files are just very large because of quantity of data contained therein.

    There could also be other issues causing the file size to bloat. Are the files set up in Shared mode, ie multiple users can have Write access at the same time? The Shared feature in Excel is notoriously unreliable and one of the corruptions it can cause is file size bloating.

    Rob H

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now