Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting & Deleting rows can take ages in Excel 2000
I have created an Excel application for Engineering Quality Document (FMEAs). These documents have: lots of merged cells, no more than 20 columns generally less than 200 rows. In Excel 97 the application ran fast enough not to be annoying. The same code in Excel 2000... well you have many forced coffee break waiting for it! (Some functions can take 20mins on a Pentium 3, 1GH with 256Mb Ram, which used to run in less than 1 min) I have looked at the code in great detail and have discovered that 99 + of the time, the code is waiting for Excel to insert or delete row. I use the very basic lines of code: Cells(x,y).EntireRow.Delete Cells(x,y).EntireRow.Insert Is there anything that can be done to speed this up ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting & Deleting rows can take ages in Excel 2000
MJBD,
Sort your rows first based on your deletion criteria, then delete them all at once. I have post code previously that does just that, so do a google groups search on deitrick entirerow.delete sort =IF group:microsoft.public.excel.* and read the first post. HTH, Bernie MS Excel MVP "MJBDeane" wrote in message ... I have created an Excel application for Engineering Quality Documents (FMEAs). These documents have: lots of merged cells, no more than 20 columns, generally less than 200 rows. In Excel 97 the application ran fast enough not to be annoying. The same code in Excel 2000... well you have many forced coffee breaks waiting for it! (Some functions can take 20mins on a Pentium 3, 1GHz with 256Mb Ram, which used to run in less than 1 min) I have looked at the code in great detail and have discovered that 99% + of the time, the code is waiting for Excel to insert or delete a row. I use the very basic lines of code: Cells(x,y).EntireRow.Delete Cells(x,y).EntireRow.Insert Is there anything that can be done to speed this up? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting & Deleting rows can take ages in Excel 2000
I do not use 2000 so I do not know if this will work but these are som of the things I do to increase speed Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Rows(x).Delete shift:=xlUp Rows(x).Insert shift:=xlDown Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = Tru ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting & Deleting rows can take ages in Excel 2000
Thanks for the tips mudraker. I had not thought of setting calculation to xlcalculationmanual and i should speed up some of my other macros working in sheets with lots o formulas - much appreciated. Forcing shift:=xlUp appears to slow down the deleting process rathe than speed it up. The calculation state does not appear to make any difference but tha may be related to the number of formulas on the sheet (not many).. this could help elsewhere. I'm still looking for ways to speed this up.. ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting & Deleting rows can take ages in Excel 2000
Changing your screenview to 'normal' will also increase speed.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting and Deleting Rows in Excel | Excel Worksheet Functions | |||
Speed of Excel When Inserting & Deleting Rows | Excel Discussion (Misc queries) | |||
inserting deleting rows?how do i get the other sheets to do the sa | Excel Discussion (Misc queries) | |||
Excel links & inserting/deleting rows | Excel Discussion (Misc queries) | |||
Updating, Deleting and inserting rows over two Excel Sheets | Excel Worksheet Functions |