Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro slow down
I have a macro which checks and merges and deletes certain lines of data
depending on the details they hold. As this macro progresses, it starts to slow down. I have tried several things including... Application.cut.copymode = false .... but nothing works. I have pinned it down to one line in the code, a simple entirerow.delete command which can take up to 20 seconds to execute. Does anyone know why this is happening, and how it can be rectified? It really gets bad with about 600 lines left to delete, and i can't wait around for 1/2 an hour to do the last little bit! Thanks in advance for all your (anticipated) help... Jonny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro slow down
One way is to delete all the lines at once:
Dim rDelete As Range Dim rCell As Range For Each rCell In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) If rCell.Value = "Delete Me" Then 'use your own criterion If rDelete Is Nothing Then Set rDelete = rCell Else Set rDelete = Union(rDelete, rCell) End If 'Do merge stuff here End If Next rCell If Not rDelete Is Nothing Then rDelete.EntireRow.Delete In article , "Jonny" wrote: I have a macro which checks and merges and deletes certain lines of data depending on the details they hold. As this macro progresses, it starts to slow down. I have tried several things including... Application.cut.copymode = false ... but nothing works. I have pinned it down to one line in the code, a simple entirerow.delete command which can take up to 20 seconds to execute. Does anyone know why this is happening, and how it can be rectified? It really gets bad with about 600 lines left to delete, and i can't wait around for 1/2 an hour to do the last little bit! Thanks in advance for all your (anticipated) help... Jonny |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro slow down
And maybe excel is trying to determine the location of those page break dotted
lines: You might be able to fix your code with a line like this at the top: ActiveSheet.DisplaypageBreaks = false You may also want to turn calculation to manual, do the deletes, then turn it back to what it was before (automatic??). Jonny wrote: I have a macro which checks and merges and deletes certain lines of data depending on the details they hold. As this macro progresses, it starts to slow down. I have tried several things including... Application.cut.copymode = false ... but nothing works. I have pinned it down to one line in the code, a simple entirerow.delete command which can take up to 20 seconds to execute. Does anyone know why this is happening, and how it can be rectified? It really gets bad with about 600 lines left to delete, and i can't wait around for 1/2 an hour to do the last little bit! Thanks in advance for all your (anticipated) help... Jonny -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro slow down tremendously
I'm having a similar situation as described by Jonny, and it happen only in
Excel 2002 (even with SP3 applied) - tested on more than one PC. Setting calculation to manual or DisplaypageBreaks to false do not resolve the problem. This situation does not happen in Excel 2000 or Excel 2003. Anyone know if this is a bug in Excel 2002, or is there a workaround. Thank you. "Dave Peterson" wrote: And maybe excel is trying to determine the location of those page break dotted lines: You might be able to fix your code with a line like this at the top: ActiveSheet.DisplaypageBreaks = false You may also want to turn calculation to manual, do the deletes, then turn it back to what it was before (automatic??). Jonny wrote: I have a macro which checks and merges and deletes certain lines of data depending on the details they hold. As this macro progresses, it starts to slow down. I have tried several things including... Application.cut.copymode = false ... but nothing works. I have pinned it down to one line in the code, a simple entirerow.delete command which can take up to 20 seconds to execute. Does anyone know why this is happening, and how it can be rectified? It really gets bad with about 600 lines left to delete, and i can't wait around for 1/2 an hour to do the last little bit! Thanks in advance for all your (anticipated) help... Jonny -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
slow macro | Excel Discussion (Misc queries) | |||
Macro - very slow run in 2003 | Excel Discussion (Misc queries) | |||
Macro is very slow | Excel Discussion (Misc queries) | |||
Macro it's very Slow .... | Excel Programming | |||
Does OnTime slow macro down? | Excel Programming |