Excel 2007 Extreme Delay When Deleting Data In Rows

[klauder]

Problem: We have some rather large excel spreadsheets (142,000 rows, 190 columns) and some users that need to delete the data from rows of cells 5,000 rows + at a time. When doing this machines jump in CPU, Excel goes "not responding" and on average comes back to life 1 minute 45 seconds after pressing the delete key.

Note: We are not doing a right click and deleting the rows. This is a highlight of the 5000 rows and pressing the delete keyboard key to clear the data.

We've tried disabling the Auto Save and the Workbook Calculation changed to Manual.

Dell is no help, Microsoft is no help. This process can be replicated on multiple Dell machines and with Operating Systems of XP SP2, SP3 and Vista, machines with single drives, raided systems and even a machine with a solid state drive. All have 2GB of RAM or more.

Any ideas would be helpful.

 

[Dan Rittgers]

Have you closed all othe application that would hog RAM and second formulas are the memory hog (Especially Vlookups, and als formulas linked extraneous to the file being deleted, if possible value the fields before deleting.

 

[klauder]

Yes we tried this on various machines after clean reboots with Excel being the only thing running. Haven't tried safe mode though so I'll try that next.

 

[klauder]

Update on this. We have tried it in safe mode and with BIOS related performance items turned off. (wow did that slow things down).

The one thing that sped the process up was copying all of the data and then pasting it into a new 2007 spreadsheet. Doing that cuts the delete of 5000 rows worth of data from 1:39 down to 1:05. A big drop but nothing like the almost instantaneous responses we get on other Dell machines. Why would a D820 and D830 have such a drastic difference?

 

[Fazza]

klauder,

You lost me there: I don't know what D820 & D830 mean. Still it is not germane to my comments which are not Excel 2007 specific. And these comments may or not be helpful to you...

Are the rows in one contiguous block? In earlier versions of Excel, deleting large numbers of rows can be slow when the rows are not a single contiguous block but is fast when the rows are one contiguous block. (Say could take a minute if there are many non-contiguous rows and a small fraction of a second when only one block.) Often filtering is applied and then visible cells (entire rows) deleted. The normal workaround that I use for speed is to sort the data first, assuming it is in a table type arrangement, using the field that will be used to decide which rows to delete. Say if a field is to have records deleted where the value is 0, sort on this field first, then filter, then delete. If needing to revert to the original order, there may be a need to temporarily add another sequentially numbered field so that the original sequence can be recoverd.

Regards, Fazza

 

[PaddyD]

Nothing to say on the specifics, but in case you've not foiund your way there yet, my first port of call for memory & performance issues is:

http://www.decisionmodels.com/

...if you can't find anything on the site i'd give Charles a mail & see if he's got any insights

 

[klauder]

I found the solution! And the order of things with Dell makes sense. There is an Add-in for Google desktop inside Office 2007. For some reason this causes the slowdown when deleting rows.

The Fix:
Open Excel
Open Excel Options
Click on Ad-Ins Tab
At the pull down menu choose COM Ad-Ins and click Go
On the list uncheck the "Google Desktop Office Addin" and click OK.

That's it. My test of deleting thousands of rows of data is now instantaneous.

Now I don't know whether to be upset with Google or Microsoft for not catching and detailing this issue. But since I found the answer in Google groups about a clear macro then I'll lean towards Microsoft.

 

[PaddyD]

Glad you got it sorted.

 

[Fazza]

Yes, many thanks for posting back with the solution, klauder. Regards, Fazza

 

[IronCityEric]

This post (re: google com add-in) really helped me (my wife) out, thanks. I think it's installed as part of Google desktop.