opening excel workbook Excel 2007 problem

[Ngolem]

I have a very old workbook that has grown like a cancer. It has about 15 pages that interact with each other passing data from one page to another through automatic updates from a data entry worksheet in the workbook.

the workbook has performed very well over the years and graduated from Excel 2003 to Excel 2007 with no problems initially and opened in less than 10 secs in the past....but now it is taking in excess of 30secs to open (but still <10secs to save). I don't have issues with opening other Excel workbooks...just this one so it is something in the workbook design itself...not Excel 2007.

I would like to troubleshoot this sheet to find out where the bottleneck exists...I do not believe I am linking to other spreadsheets now though I did try that at one time...I think those links are gone.

I would like a set by set method of checking out this workbook if someone would be kind enough to direct me

I use XP SP3 as an operating system

 

[SkipVought]

hi,

One of the things that I found that seems to slow thing down, is saving the workbook as an Excel 97-2003 .xls (compatablity mode) workbook. Save it as a 2007 workbook! Either .xlsx or .xlsm is you have macro code in your workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ngolem]

no...that is nope the problem. I installed Excel 2007 years ago and have converted my workbooks to .xls without any problems in the past, I have no macros in the workbook. This workbook has been in existence for over 5 years...It has just been giving me aggravation in the last 6 months or so.

Jim Broadbent

 

[SkipVought]

is you workbook on a network? Sometimes there are environmental issues that are beyond your control.

I installed Excel 2007 years ago and have converted my workbooks to .xls without any problems...

????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ngolem]

no...it is on a desktop connected to the internet but I am the single user and no network involved.

I was wondering if I could reconstruct the workbook not by copying the exact worksheet but copying the data from a worksheet instead to a new workbook worksheet, page by page but starting from the bottom of the data flow and moving upward. I might try that....

I checked for name ranges in the worksheet but I have none. I recall vaguely something about doing searches for ole links but forgot how that was done....there should be none now.

I have only one chart in the workbook...

Jim Broadbent

 

[Hoaokapohaku]

You will probably be able to solve your problem following the suggestions here:

http://www.ozgrid.com/Excel/ExcelProblems.htm

 

[Ngolem]

Ok...I finally determined what the problem is...this is a workbook where I keep all my stock transaction records in and I have an "Overall" worksheet with 10 years of data plus a chart showing the state-of-the-union.

There is a fair bit of data there (weekly summaries) but what is the problem is that it had the full 10,000+ rows of the spreadsheet as having data where in reality there are only 600 rows with real stuff in it. I have been trying to reduce the unused rows and columns to no avail. (my poor machine is working overtime trying to reduce the rows.

I think I will just transfer the "good cells" to another blank worksheet and see if that helps.

Anyway, at least I know the source of the problem now.....thanks for your help.

Jim Broadbent

 

[SkipVought]

There is a fair bit of data there (weekly summaries) but what is the problem is that it had the full 10,000+ rows of the spreadsheet as having data where in reality there are only 600 rows with real stuff in it. I have been trying to reduce the unused rows and columns to no avail. (my poor machine is working overtime trying to reduce the rows."

Don't understand why deleting rows would cause such a problem!

There is the DELETE key which only deletes values, but does NOT delete all DATA!!!

Select the row to delete

Right-click and select DELETE.

Now ALL the data in those rows is gone!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ngolem]

Ok...knocked the opening time from about 45 secs to 2 secs with the same data

I created a new sheet and just copied the rows and columns (the cells only) with data to the new sheet.

Since this was a final destination there was no problems with updating other sheets from that one and the addresses from data updates from other sheets were unchanged.

I then entered updated share values for my portfolio and confirmed that the new sheet updated the same as the older larger one (and it did)

Deleted the old sheet and saved the new workbook under a modified name so the original was untouched.

Reopened the new version and it opened in 2 secs flat....yahoo!!!!

I think my problem developed through sloppiness. Instead of copying and pasting just the cells of a row with data, I would highlight the whole row and copy it. when I formatted a column I format not just where the data was but beyond. At some point I must have copied a blank into all the unused cells in the entire worksheet all 10,000+ rows and whatever columns....I could not perform a delete on these unused rows/columns with my machine....too much for it for some reason

But copying and pasting the used cells to a new sheet eliminated all my problems.

Don't know how this happened but it is resolved now

Thanks for letting me talk my way through this problem.

Jim Broadbent