Macro slow down

[Guest]

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

 

[JE McGimpsey]

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

 

[Dave Peterson]

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??).

 

[Guest]

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.