Reduce File Size

[aabbasi]

Hi:

Is there a way to reduce the Excel file size considerably? This excel file is huge with many sheets. I do not like to use Zip function.

Thank you

 

[Smitty]

Just add a procedure to unprotect/protect to the code:

<font face=Tahoma>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wks <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            <SPAN style="color:#00007F">With</SPAN> wks
                .Unprotect "xyz"
                AnyMerged = wks.UsedRange.MergeCells
                <SPAN style="color:#00007F">If</SPAN> AnyMerged = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                    myLastRow = 0
                    myLastCol = 0
                    <SPAN style="color:#00007F">Set</SPAN> dummyRng = .UsedRange
                    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
                        myLastRow = _
                        .Cells.Find("*", after:=.Cells(1), _
                        LookIn:=xlFormulas, lookat:=xlWhole, _
                        searchdirection:=xlPrevious, _
                        searchorder:=xlByRows).Row
                        myLastCol = _
                        .Cells.Find("*", after:=.Cells(1), _
                        LookIn:=xlFormulas, lookat:=xlWhole, _
                        searchdirection:=xlPrevious, _
                        searchorder:=xlByColumns).Column
                    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
                    
                    <SPAN style="color:#00007F">If</SPAN> myLastRow * myLastCol = 0 <SPAN style="color:#00007F">Then</SPAN>
                        .Columns.Delete
                    <SPAN style="color:#00007F">Else</SPAN>
                        .Range(.Cells(myLastRow + 1, 1), _
                        .Cells(.Rows.Count, 1)).EntireRow.Delete
                        .Range(.Cells(1, myLastCol + 1), _
                        .Cells(1, .Columns.Count)).EntireColumn.Delete
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                .Protect "xyz"
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> wks
</FONT>

Smitty

 

[jimscarver]

lost charts

The macro worked great except that on three sheets I lost the chart when I ran it. After adding the charts back the size of the file was 1/4 the size and macros that iterated over the size of the sheets ran much faster.

Why do I lose some, but not all of my charts? How can that be prevented.

Thanks.

 

[ufischer]

<table id="post315607" class="tborder" align="center" border="0" cellpadding="6" cellspacing="0" width="100%"><tbody><tr valign="top"><td class="alt1" id="td_post_315607" style="border-right: 1px solid rgb(255, 255, 255);">
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'From: http://www.contextures.com/xlfaqApp.html#Unused ' Re-set used range Dim myLastRow As Long Dim myLastCol As Long Dim wks As Worksheet Dim dummyRng As Range For Each wks In ActiveWorkbook.Worksheets With wks myLastRow = 0 myLastCol = 0 Set dummyRng = .UsedRange On Error Resume Next myLastRow = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ searchdirection:=xlPrevious, _ searchorder:=xlByRows).Row myLastCol = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ searchdirection:=xlPrevious, _ searchorder:=xlByColumns).Column On Error GoTo 0 If myLastRow * myLastCol = 0 Then .Columns.Delete Else .Range(.Cells(myLastRow + 1, 1), _ .Cells(.Rows.Count, 1)).EntireRow.Delete .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete End If End With Next wks End Sub</pre>
HTH

Mike
</td> </tr> <tr> <td class="alt2" style="border-right: 1px solid rgb(255, 255, 255); border-width: 0px 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(255, 255, 255) rgb(255, 255, 255); -moz-border-top-colors: none; -moz-border-right-colors: none; -moz-border-bottom-colors: none; -moz-border-left-colors: none; -moz-border-image: none;">

</td> <td class="alt1" style="border-right: 1px solid rgb(255, 255, 255); border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: -moz-use-text-color rgb(255, 255, 255) rgb(255, 255, 255) -moz-use-text-color; -moz-border-top-colors: none; -moz-border-right-colors: none; -moz-border-bottom-colors: none; -moz-border-left-colors: none; -moz-border-image: none;" align="right"> </td></tr></tbody></table>
I had to put another If statement in the above for sheets which more than 250 columns occupied so that it didn't try to delete those last few columns in that case. You could probably get away with just bypassing


if mylastcol > 255 then
else
.Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete

end if

</pre>
the code that clears out unused columns as in the example just above this text. On my workbook, I had several sheets that used all 256 columns and when the macro got to those, it blew up because now it was trying to look at columns beyond column IV.

 

[ssingh75]

I am getting an error on below line

"Application defined or object defiened error"


.Cells(1, .Columns.Count)).EntireColumn.Delete

 

[Dan88]

Hi All. I am trying to use this code as well but not sure if im doing it right.
I copied entire thing and insert this int he project side of my VBA under a new module.

but when i hit run, nothing happens and my file is still >15mb for no apparent reason.

Can you please help me how to use this?
Thanks in advance.