Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Someone send me an Excel file that's 10MB, and thus causes us email problems. When .zip'ed it goes to under 1MB, so presumably there's something wrong with the Excel sheet but is there any way to compress this somehow?

I have a range of versions of Excel available to me by the way.

share|improve this question
    
I'm confused. " When .zip'ed it..." with "... is there any way to compress this somehow?". It seems that you have answered your own question. –  EBGreen Apr 25 '12 at 14:58
    
You're right, I could have been clearer sorry, I mean 'actually make the .xls file smaller' sorry - as opposed to just using winzip/7zip 'externally' –  Chopper3 Apr 25 '12 at 15:02
    
Aah...as far as I know there is no built in compression. I am prepared to be wrong however. –  EBGreen Apr 25 '12 at 15:06
    
Is there really that much data in the workbook, or is this a case of the million empty rows problem? –  Excellll Apr 25 '12 at 15:34
    
@Excellll very much the latter I believe, it's not my excel sheet and its 'owner' is a bit clueless. –  Chopper3 Apr 25 '12 at 15:36

1 Answer 1

up vote 4 down vote accepted

If there is space that can be 'reclaimed', there are several different things you can try:

  1. Reset all the Used Ranges in each workbook. This can be done using a VBA method like this:

    Public Sub ResetAllLastCells()

    Dim wks As Worksheet
    Dim i As Long
    
    For Each wks In ActiveWorkbook.Worksheets
        i = wks.UsedRange.Rows.Count
    Next wks
    

    End Sub

  2. Remove any unwanted custom styles. I use a VBA method as below to remove all custom styles:

    Public Sub StyleKill()

    Dim s As Style
    
    For Each s In ActiveWorkbook.Styles
        If Not s.BuiltIn Then
            s.Delete
        End If
    Next s
    

    End Sub

  3. If there is VBA code within the workbook, try saving it decomiled. You can do this by opening the workbook, open the VBE, make a small change (add white space or something) and then, without compiling the project, save the workbook. This can dramatically reduce the file size.

I'm sure there are other ways too but these are my tried and tested ways. In one workbook I have this consistently reduces the file size from ~15mb to 10mb.

share|improve this answer
    
Thank you very much indeed, I'll give it a try. –  Chopper3 Apr 25 '12 at 15:51
    
By the way, this did help a little - think we might have to do the whole 'transfer everything to a new file' thing I guess, cheers anyway. –  Chopper3 Apr 26 '12 at 10:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.