Excel 2007 Extreme Delay When Deleting Data In Rows

klauder

New Member
Joined
Jul 9, 2008
Messages
4
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Dan Rittgers

Active Member
Joined
Dec 28, 2007
Messages
415
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

New Member
Joined
Jul 9, 2008
Messages
4
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

New Member
Joined
Jul 9, 2008
Messages
4
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Jul 9, 2008
Messages
4
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
Yes, many thanks for posting back with the solution, klauder. (y) Regards, Fazza
 

IronCityEric

New Member
Joined
Nov 21, 2008
Messages
2
This post (re: google com add-in) really helped me (my wife) out, thanks. I think it's installed as part of Google desktop.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,871
Messages
5,465,192
Members
406,416
Latest member
Revolution_72

This Week's Hot Topics

Top