Speed Is What I Need

Posted by Jon Griggs on August 07, 2001 6:51 AM

I have some macro code that works, but is excruciatingly slow. I have disabled screen updates and changed the calculation mode to manual. (Yes, the first person to actually try to use the archives ;-} ).
The code is as follows.

Sub DelRows()
'
' Macro recorded 8/7/01 by jgriggs
'

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For x = Range("N1").End(xlDown).Row To 1 Step -1
If Cells(x, 13) = True Then
Rows(x).Delete Shift:=xlUp
End If
Next x
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

This is the pertinent subset.
The only thing I left out is a whole set of ORs and ANDs to make the decisions this subset is operating from.
The code execution went from an (extrapolated) 20 hours down to 4 hours with the addition of the first two lines (running on 20000 lines).
I would appreciate any help to get this lower.

Thanks
Jon Griggs

Posted by Rob Jackson on August 07, 2001 8:20 AM

A couple of thoughts, could the data be sorted first in to true /false then just delete to true section as a lump. Alternately, why not use the advanced filter to extract only the false files. A third option is to scan for groups of trues then delete as a lump rather than lots of individuals. (eg find a true at line 300 scan up to 250 as all true then delete Rows(250:300)delete etc)

Just a few thoughts.

Rob

Macro recorded 8/7/01 by jgriggs For x = Range("N1").End(xlDown).Row To 1 Step -1 If Cells(x, 13) = True Then Rows(x).Delete Shift:=xlUp End If Next x Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub

Posted by Jon Griggs on August 07, 2001 9:34 AM

Deleting in lumps may work.
If I were to do that, for example, for 5 lines how would I modify the line below to accomodate that?

Rows(x).Delete Shift:=xlUp

I changed it to
Rows(x:x-5).Delete Shift:=xlUp
but the compiler doesn't like my syntax.

Thanks,
Jon Griggs

Macro recorded 8/7/01 by jgriggs : Application.ScreenUpdating = False For x = Range("N1").End(xlDown).Row To 1 Step -1 If Cells(x, 13) = True Then Rows(x).Delete Shift:=xlUp End If Next x Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub : This is the pertinent subset.

Posted by Rob Jackson on August 08, 2001 12:42 AM

Before your For loop you need to define and set a boolean variable QuickScan to FALSE. and an integer variable FirstRow as 0.
OK inside your for loop you need:

if QuickScan and Cells(x, 13) = false then
Rows(FirstRow & ":" & (x-1)).Delete Shift:=xlUP
QuickScan=false
elseif QuickScan=false and Cells(x, 13) = True then
QuickScan=TRUE
FirstRow=x
end if

Basically what this does is scan through until it finds the first TRUE it then sets the FirstRow value as this and sets the QuickScan Value to TRUE. This means it is now looking for the upper limit of the band of TRUE's When the value turns back to FALSE, the DEletion is made from the First Row value to the line below the current one in the scan. (The last TRUE) it then sets QuickScan to FALSE. This will stay this way until the next TRUE is found. I haven't tested it but it should work.

Rob

Macro recorded 8/7/01 by jgriggs