Cleaning and Making an Excel file Efficient

[wherdzik]

In our organization we have a very large file with lots of tabs, links, statistics and lots of formulas. It was 30 MB before i saved it in binary format, now its about 10MB. I honestly do believe that there are things in there that still could be cleaned up but I just don't know where to begin. I know changing the format to binary was a big step but I am also wondering if there is anything else in there that I can do to clean up this file so that its as small as possible and smooth as possible. It crashed on me a few times and once I roll this out I don't want it to be a regular thing. This file relates to employees compensation so its obviously highly sensitive. If anyone has a utility or any experience with this that could provide some experienced help I would greatly appreciate your help.

Thank you for letting me post.

[JBeaucaire]

From this old thread:

1) Use the SELECT OBJECTS option and draw large selection areas, see if there are some hidden objects on the workbook that are being duplicated invisibly.

2) Instead of deleting formulas, do a CLEAR ALL on the cells outside the data range.

3) Conditional formatting rules can spin out of control

Here's an old macro I used a few times to reduce the filesize on some troublesome workbooks:

Please Login or Register  to view this content.

[wherdzik]

Jerry Forgive me but I am a macro novice. do I need to change ---- fSize = FileLen(ThisWorkbook.Name)----- to the name of my file?
I am getting an error. I have a lot to learn about Macros. I know only the very very very basics - if that.

[JBeaucaire]

No, the ThisWorkbook.Name is a builtin function and will fill itself in based on the workbook in which you install the code.

To add the code:

1. open the VBEditor (ALT+F11)
2. Insert a new module (Insert > Module)
3. Paste the code into the module that appears

Run the macro and it should do it's thing. When you receive errors, DEBUG and make note of the line of code that is highlighted in yellow, we need to know what that is to guess at what might be wrong. But it's a pretty basic macro.

The version of the macro I gave you uses column A and row 1 to determine the boundaries of the data.

Here's another version that uses a "search" method on each sheet, it might work better:

Please Login or Register  to view this content.

[wherdzik]

I ran the last code and it was running and then it said:

Run time error '11004': Cannot change part of a merged cell.

When I pressed Debug it then highlighted the following line.

Range(Cells(i, Columns.Count).End(xlToLeft).Offset(0, 1), Cells(i, Columns.Count)).Clear

When I looked at some of the tabs it looks like it changed the structure of some of the worksheets. Looks like it un-merged some things.

[JBeaucaire]

I hope you're testing on a COPY of your file.

Odd, the second one is designed to find the last used cell on each sheet, then do a CLEAR ALL on all the cells outside that used range.

Merged cells are always a bit of a pain. But I'm at a loss without seeing your workbook as to why the macro is even affecting the used portion of the sheets. I'd have to see it to uncover what's happening.

If you want to share a link to your file secured on a OneDrive or something, I'd be happy to check it out. You can PM me with the link.

[wherdzik]

Thank you for trying to help.