Remove Macros Completely

[theamericanpilot]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I believe that after creating a macro on my Excel, the size of the file got huge (28Megs). I removed the macro but the size remains too large. What can I do to return to a smaller, more manageable file size.<o></o>
<o></o>
Could protecting the sheets and the workbook have anything to do with the size of the file also?<o></o>
<o></o>
<o></o>
Thanks.<o></o>
<o> </o>

 

[SamTYler]

On each sheet, Select all columns after the last used column and delete them. Then do the same for all rows under the last used row.

Use ctrl+shift+arrow to rapidly select all that.

 

[konew1]

Follow the suggestion in the previous post or just use END+HOME to see if that takes you to the last row and last column of data. If it doesn't then follow the advice in Sam's posting. You will have to save the file to make the deletions reduce the file size and reset the place End+Home takes you to

 

[AMAS]

Just a quick note. Unless your macro is of an unusual size, its not the one causing your file size problems. One quick way is to check is to save the file to macro-free extension (e.g. xlsx instead of xls or xlsm) that way you are sure the macros are the not the source of the huge file size.

AMAS

 

[theamericanpilot]

Thanks for your replies.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Saving the file after deleting all those cells did not change the size of the file, but saving in a xlsx format helped. I went from 28Megs to 8Megs.

However the file has to go through a conversion (I think it’s because of I use Excel 2003 and a xlsx file is in Excel 2007 format). Beside that fairly moderate inconvenience, I am happy with the result.<o></o>
<o></o>
Thanks<o></o>

 

[AMAS]

If you are using Excel 2003, check which of the save extensions is macro-free. I don't know them by heart, but I know that xls files can contain macros (maybe binary???). I'm glad that it worked out, but you should really investigate where that huge size is coming from. Also remember that xlsx is a compressed file type and so is smaller in nature than xls. Could this be the reason that file got smaller????

Good luck,

AMAS

 

[SamTYler]

"FOR" is usually the reason for unexpectedly large Excel files.

"FOR" = FORmulas and FORmats.

Save a copy of the file after removing all Formats.

Where Formats are critical and it is possible, Format entire Rows or Columns. Change all merged Cells to "Center Across Selection" Formats.

VBA Macros.Character.Counts take up the same file space as Excel.Formulas.Character.Counts where the Counts are equal. IOW, a lot of long, identical formulas can take more file space than a short macro to do the same thing.

Embedded pictures take a lot of file size.

 

[Johnny Cash]

fwiw, and for the sake of the next searcher... I deleted my macros in 2003 by

TOOLS>MACRO>Visual Basic Editor

then I opened

TOOLS> MACRO>

and deleted all the Macros in list box

 

[theamericanpilot]

Thanks you all for your precious replies.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
SamTYler was able to explain the reason for the large file. Removing the Macros did not help reducing the file size. The numerous formulas and formatting were the culprits.<o></o>
<o></o>
Thanks again.<o></o>