Discussion Locked

Excel 97 - Lower Right Cell/End of Sheet

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

We use Office97 extensively. The accounting dept recently showed me a problem they've been having in a workbook. Last year's copy of a workbook loads in about 2 seconds. This year's copy loads in about 15 seconds, with half as much information.

I've been able to determine that the difference between the books is file size. The file size of this year's book is extremely bloated. I can press CTRL-END on each page of the sheet, and it move to a cell down between row 6700 to 65000 depending on the page. The max number of rows we actually use on each page is around 150. Obviously Excel is storing huge amounts of unused blank cells, but I cannot seem to find a way to reset the "last cell" on a page. I've tried deleting rows, deleting cells, clearing contents. Nothing works. Excel help file and Microsoft KB is less than helpful on this issue.

Any ideas? This workbook should max out around 700k, yet it's currently showing up at 7MB, and growing.

8 replies

by dklippert

I'm sure you've looked at this article, but if not, type the following in the address box of IE
MSKB Q230998
It's not only values that confuse Excel, but also formatting, including changes in Row height.
If all else fails, you could also try copying to another workbook.

by bpalmer

Thanks, both of these suggestions helped. The first answer, the MSKB article applies to Excel2000, but I found it works in Excel97 also. The VBA solution is the much easier solution tho.

by sankulpune

Hello bpalmer,

It seems, something somewhere has gone wrong while copying data from the old file to the new file.

To overcome this problem, insert a blank sheet in the workbook and paste the data from existing worksheet to this worksheet. After confirming proper transfer, delete the old worksheet.

You need to repeat this procedure for each 'corrupt' worksheet.

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

by bpalmer

Thanks for the suggestion. Copying the data from the old workbook to the new DOES clear up the last cell problem, but doesn't allow the special column widths to copy over. These sheets are very wide, and have a different column width on just aboutevery other column, so this really won't work for us except as a last ditch effort.

by juanita marquez

If you have a workbook of that size with no logical explanation, I would suggest a few things:

1. Make sure it is not infected with a virus. Scan the file with the latest antivirus program to see if there are any macros which may be hiding. If it is infected, you will have to find the creator of the file and clean the user's master Excel program because anything they create will be infected.

2. Try unhiding pages. You may find extraneous pages that were somehow added, and delete them.

3. Check to see if there are any macros NOT virus-related via Tools->Macro->Macros... Perhaps one of your users has macros in their own Excel master and they somehow were saved to this workbook.

4. If none of these help, you can ensure a default setting reset by removing Book.xlt and/or Sheet.xlt from the XLStart folder (do a search, but is likely found in C:\OS\Profiles\username\Application Data\Microsoft\Excel\XLStart) or renaming them and moving them to another folder temporarily. If you don't find these files there, go to Tools->Options->General->Alternate Startup File Location and pull them from that location. Once you've removed those two files, the system will reset and you can create a new workbook. Copy a worksheet and Paste Special (Edit->Paste Special...) into the new workbook, selecting ONLY formulas and values, so you will leave out any odd formatting. Do this for the whole book, then save it. It should be smaller in size. Then save the new one and delete theold one.

Good luck.

by bpalmer

Thanks. I did all of these things before I started getting frustrated with the book. These are good tips, things to try beforehand, but didn't solve this exact problem.

This discussion is closed to new replies.