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.

 

[oxojim]

I see that I'm really late to the party, but I wanted to add my thanks. This was crippling my ability to do some of the work I needed to. What a relief!! Thanks a million.

 

[finchd4]

New question:

I thought this somewhat was related, but I dont have that add in at all.

Weekly I am adding about 500-800 rows of 20 columns to a master table in a sheet. Yes a table. I copy from another spreadsheet and paste special values into mine. It takes about 2 minutes for ~200 rows of data.

Yes my table has conditional formating, and calculations to it. No Arrays.

Yes I have tried turning the formating off and calculations to manual, with no better results.

I am convinced that since when adding more rows the table automatically expands its range, that that is slowing it down.

I tested this by just expanding the table range to entire column, 65k rows. My intent was to do the same paste soecial values into a already expanded table, and see if it was more instantaneous.

So far I have not been able to test it with the paste special since the expansion alone crashes excel.

Any thoughts?

 

[oxojim]

I haven't worked with Excel tables, so I can't address that to whatever extent it's part of the problem.

I'd start by detemining if the problem exists outside of a table format and, if so, what might eliminate it. I'd try to replicate the data layout and transactions in a regular worksheet format and see if the same problem persists.

First concern is your reference to 65k rows. If possible, you might as well upgrade to Excel 2010. It's possible that whatever the problem is, it might be resolved in newer software.

Next, try turning off, or removing things like conditional formatting.

I find Excel to be a pretty good database tool. I routinely update worksheets with 20,000+ rows of updates and over 30 columns for a total of 150,000+ rows when updated, with little or no delays in updates. However, there are no formulas - only raw data. I use filters and pivot tables, mostly, to extract and profile the data.

In the past, other worksheets with lots of rows with formulas (conditional formatting?) took so much time to update after a sort, or any transaction, that I moved more toward database type activities and performed calculations on subsets of the data or used pivot tables to do so. Pivot tables offer an advantage in that they only update when refreshed - not every time an entry is made.

Hope some of this helps. Good luck.

 

[Dan Rittgers]

You have two files: File containing the table (TF) and the file having the data (DF). Everything is relative, but 2 minutes does not seem too long to spend time "designing around". Try copying the information, Paste Special into TF on a worksheet in DF. Then cut and paste from DF into the table.

 

[finchd4]

Update:

I let the file run over the weekend, since it obviousely was going to take longer than a few hours to resize the table for all rows and 20 columns. When I got back in today, it was resizred, and as you can imagine one HUGE file size. It did fix the problem, everything pasted special into the sheet really well, probably one second or less. New problem would be that the rediculus file size will take forever to just open now. For now I went back to an earlier version, since I would rather have a file that opens in a timely mannor. Any suggestions?

 

[Dan Rittgers]

What you describe happens in Assess and requires compressing the file. I have never experienced it in Excel.

I believe this process is like defragging hard drive, space remains reserved for data you delete, unlil you "compress" and Access file. I do not know the term in Excel (it might be like "refresh" for Pivot Tables).

 

[finchd4]

Just to give some people a solution to this if they are experiencing the same thing.

I am still curiouse as to why a table slows down when copying new rows of data into it, but either way I have found that if you convert the table to a range and then copy all the data into it, and then convert back to a table and copy down any formulas that were in it, that it works much quicker. There are delays when converting to and from a range and table, but the pasting is instantaneous.