dslreports logo
 
    All Forums Hot Topics Gallery
spc
Search similar:


uniqs
652
OldAuditor
join:2007-03-24
Oklahoma City, OK

OldAuditor

Member

[Excel] File size out of control

I have a workbook that is 25 sheet and several internal references among the sheets. The original file size was about 1.8 MB.

I ran the "Clean Excess Formatting" from the inquire module. When it was done, the file size had grown to 61+ MB. Several of the sheets (4) now have the last active cell as column CG and row 1,048,576, when the last active cell should be somewhere around AK:65. I cannot get the rows and columns to clear because it is telling me Excel cannot perform the operation with the resources available.

Dell Vostro 470, quad-core i7, 8GB ram, 1TB drive.

Any great ideas?

TIA

Old

Wily_One
Premium Member
join:2002-11-24
San Jose, CA

Wily_One

Premium Member

Your wording sounds similar but not exactly like this Excel add-in:
XSFormatCleaner

I'd suggest that unless you've already tried it.
»xsformatcleaner.codeplex ··· entation

Kalford
Seems To Be An Rtfm Problem.
MVM
join:2001-03-20
Ontario

Kalford to OldAuditor

MVM

to OldAuditor
You could give this a go.

1. Make a copy of your spreadsheet (keeping the original file safe. . . in case something goes wrong.)

2. Open spreadsheet and create a new worksheet (commonly called "tab").

3. Switch to an "overgrown" worksheet that is too big because of the "last active cell)

4. Select the true/desired range of data. (ie. a1:Ak65.)

5. Cut and paste that into the same cell addresses on your new worksheet.

6. Delete the original worksheet from workbook.

7. Rename new worksheet to that of the original (now deleted) worksheet.

8. Check formulas to make sure that none were broken when moving your data. (also check/verify visual basic scripts - if applicable)

9. save file.

10. Repeat for any other worksheets "where the last active cell" has become out of sync with your true/desired data range.

Msradell
Premium Member
join:2008-12-25
Louisville, KY

Msradell

Premium Member

said by Kalford:

You could give this a go.

1. Make a copy of your spreadsheet (keeping the original file safe. . . in case something goes wrong.)

2. Open spreadsheet and create a new worksheet (commonly called "tab").

3. Switch to an "overgrown" worksheet that is too big because of the "last active cell)

4. Select the true/desired range of data. (ie. a1:Ak65.)

5. Cut and paste that into the same cell addresses on your new worksheet.

6. Delete the original worksheet from workbook.

7. Rename new worksheet to that of the original (now deleted) worksheet.

8. Check formulas to make sure that none were broken when moving your data. (also check/verify visual basic scripts - if applicable)

9. save file.

10. Repeat for any other worksheets "where the last active cell" has become out of sync with your true/desired data range.

While the solution you provided would certainly work it's far from being a simple clean solution to the problem. It's just using brute force to overcome something. Since the OP has 25 pages in his workbook it's going to take forever to do what you're suggesting.
OldAuditor
join:2007-03-24
Oklahoma City, OK

OldAuditor to Kalford

Member

to Kalford
said by Kalford:

Check formulas to make sure that none were broken when moving your data. (also check/verify visual basic scripts - if applicable)

I used a lot of named ranges. Will following this procedure break the names?

Kalford
Seems To Be An Rtfm Problem.
MVM
join:2001-03-20
Ontario

1 edit

Kalford

MVM

Range names will follow the data/cells to the new worksheets when you use the cut and paste method.

(translation: shouldn't be any problems. )

Note: an alternative (and possibly easier/quicker) solution you could try would be, on each sheet, to delete any rows and columns that are below and to the right of where your desired/true data is stored. . . in theory it works (excel quirk alert - but not all the time)

however this method may crap out with the same sort of "out of resources error" that you ran into before.

A possible way to get around the memory issues with this method would be to delete groups of rows in smaller chunks (instead of all at once), starting from the bottom row and working up. . .and then repeat for "chunks" of columns starting from the rightmost column and working left.

H2OuUp2
Happy to be here
Premium Member
join:2002-03-15
Oklahoma City, OK

H2OuUp2 to OldAuditor

Premium Member

to OldAuditor
If you had text boxes, controls, pictures, etc. This could be your problem. I will find in many cases the boxes will be replicated multiple times, some even hidden. I have to write code to delete all the images, text boxes (even hidden ones) from the sheet.

Also make sure your range names don't refer to the last cell in the workbook. If you don't make some range names as absolute, they tend to reference the last row instead of what you originally put in there.