Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2990
  • Last Modified:

How to reduce excel file size

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
0
piaakit
Asked:
piaakit
1 Solution
 
Peter HausslCommented:
Hi,

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
http://www.wikihow.com/Reduce-Size-of-Excel-Files

Maybe one of those is moving you forward.

br
Peter
0
 
Glenn RayExcel VBA DeveloperCommented:
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
        Rows(r).Delete
        r = r - 1
    Loop
     
    intLastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    Application.ScreenUpdating = False
    c = intLastCol
    Do Until Application.CountA(Columns(c)) > 0
        Columns(c).Delete
        c = c - 1
    Loop
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
    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
        Else
            myStyle.Delete
        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
            myName.Delete
        End If
    Next myName
End Sub

Open in new window


-Glenn
0
 
Rob HensonIT & Database AssistantCommented:
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.

Thanks
Rob H
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now