This sheet is very slow to respond, can anyone advise ?

[Trebor777]

HI

I have a complex workbook (14 sheets) which works fine. One sheet is extremely slow to respond. I don't know why.

I have removed all functions from the sheet using copy/paste values.

I have removed all formatting, setting all numbers to integers.

I have removed all the other sheets. All I have now is a single sheet of integers.

It is still very slow to respond. I often get 'Not Responding' error messages eg when saving the file.

I have excel 2007 running on a decent Windows 7 pc.

My instinct is that there is something embedded within the file , but I don't know.

Is this a problem with the sheet, or my PC ??

Thanx. Trebor

[Fluff13]

Try Ctrl G > Special > objects
& you'll find a shed load of them

[MarvinP]

Hi Trebor,

I agree with Fluff above. You have lots of "Objects" on this sheet. Follow instructions at:
https://www.extendoffice.com/documen...-pictures.html
to delete them.

I got myself into this one time by using copy and paste of cells, not knowing I was pasting objects also. Pretty soon I had LOTS of zero size objects (couldn''t see them) all over my sheet.

[Fluff13]

Ad you've got over 49,500 of them, depending on your system, you might want to delete them in batches.
Saving the file each time.

Please Login or Register  to view this content.

[MarvinP]

Hi Trebor,

Start Visual Basic editor and show the Immediate Window. In it type this:
?Activesheet.Shapes.Count and then press enter.
It shows you have 49554 shapes in this sheet.
No wonder it takes so long to refresh.....

Here is some code to remove those Shapes (which happen to be TextBoxes)

Please Login or Register  to view this content.

Step through the above code a few times and have the immediate window open to see what it does.

[Trebor777]

Guys mega-thanx & I am sure you have found the problem, I'm struggling.

The 1st solutions above just seem to hang my PC

The later ones, using macros & vba stuff, sorry, I don't understand.

I will persevere.

Where did these 'objects' come from & what are they? I did copy some functions from excel help files ?

mega-thx ... Trebor

[Trebor777]

The only way I could have got all these objects is by copying a row or cell from elsewhere, then copying them down for 365 cells or more. If I delete all but the 1st few rows, would my pc be able to cope? I could then copy the un-polluted cells back down again ?

[Fluff13]

You might be better off just deleting a few rows at a time, rather than the whole lot.
If your interested in either of the macros, have a look here for how to do it.http://www.contextures.com/xlvba01.html

[sandy666]

if i see well there is no any formulas but data only
so select data and copy to a new workbook
or

1. copy data
2. create new sheet
3. paste data there
4. delete old sheet

both version attached

[avk]

My advice without knowing anything else:
Boot to safe mode and see if it happens there.
Update/patch Office 2007 (and whatever flavor of Windows the client is running for that matter) in full, then retry.
See if there are any driver updates for the graphics card.
Additional questions: Does Excel behave this way with any file, or is it a particular file? And are these files local to the user's machine, or are they stored somewhere on a network drive? Excel 2007 has had performance issues in the past if the spreadsheets were on a network drive, but they released a hotfix/patch that fixed it.

You could also try starting the Excel application in safe mode (not Windows safe mode -- Windows regular mode, then launch Excel in safe mode), in case there are 3rd party plug-ins that could be affecting application performance. https://support.microsoft.com/en-us/kb/919196

[Trebor777]

Ad you've got over 49,500 of them, depending on your system, you might want to delete them in batches.
Saving the file each time.

Please Login or Register  to view this content.

Mega-thanks to all. Problem solved !

The Goto special objects route doesn't work, the program hangs. (I've read similar comments elsewhere in Google)

I took the time to read your suggestions on how to make macros & run them, and finally got there using the macro you gave me.

Thanks again. Trebor

[Fluff13]

Glad it's sorted & thanks for the feedback