hi Dan,

I changed my test werkbook to 10 sheets and added a loop to my code
I used a bit from Rick sample for the loop.

Sub cardan()
'
' cardan Macro
'
Const WSnames As String = _
"Sheet1,Sheet2,Sheet3,Sheet4,Sheet5," & _
"Sheet6,Sheet7,Sheet8,Sheet9,Sheet10"

Dim lngStart As Long
Dim lngEnd As Long
Dim WS As Worksheet

lngStart = timeGetTime
'
For Each WS In Worksheets(Split(WSnames, ","))
WS.Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range("A6").Select
ActiveSheet.Range("$A$1:$CC$8001").AutoFilter _
Field:=1, Criteria1:="Delete"
While ActiveCell.Text < "Delete"
ActiveCell.Offset(1, 0).Select
Wend
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("A5").Select
Next

lngEnd = timeGetTime

MsgBox lngEnd - lngStart & " milliseconds"


End Sub

At my computer it needed 3 minutes and 16 seconds to complete.
I placed the ScreenUpdating inside the loop so you can see the
selection of each sheet.

HTH,


Wouter

At my computer it needed 3 minutes and 16 seconds
to complete. I placed the ScreenUpdating inside the
loop so you can see the selection of each sheet.

I believe if you move the ScreenUpdating and the Calculation statements
outside the loop (as I did in my code), your macro would execute quicker.
Also, since you seem to have a workbook set up with example data, after you
move those statements outside the loop and get a new execution time for your
code, would you do me a favor and set the data back up and use my code to
processes it... I would be curious as to the time difference between your
approach and mine (where both are run on the same computer). Thanks.

Rick Rothstein (MVP - Excel)

Hi Rick,

Today I have these results:
Your code: 203762 milliseconds
My code: 200895 milliseconds

I only tried both versions once.

Today I had MS-Word and Google-Earth active.
These seems to take some CPU time.

Wouter

Today I have these results:
Your code: 203762 milliseconds
My code: 200895 milliseconds

I only tried both versions once.

Thanks for running the test, I appreciate it. Seems like the two methods are
reasonably equivalent, speed-wise, differing by about 3 seconds out of about
200 total seconds.

Rick Rothstein (MVP - Excel)

On Mar 9, 7:31*am, "Rick Rothstein"
wrote:
Today I have these results:
Your code: *203762 milliseconds
My code: * *200895 milliseconds

I only tried both versions once.

Thanks for running the test, I appreciate it. Seems like the two methods are
reasonably equivalent, speed-wise, differing by about 3 seconds out of about
200 total seconds.

Rick Rothstein (MVP - Excel)

I can't seem to get the macro to work. I am using the combined macro
provided by Wouter. It appears the error is in the "ActiveCell.Offset
(1,0).Select" line. At least that is what is highlighted when I do
debug. Any thoughts on what I could be missing or doing wrong?

I can't seem to get the macro to work. I am using the
combined macro provided by Wouter. It appears the
error is in the "ActiveCell.Offset(1,0).Select" line. At
least that is what is highlighted when I do debug. Any
thoughts on what I could be missing or doing wrong?

Since you are using Wouter's code, I would think you should be directing
your question to him, not me, given that he would be more familiar with the
ins-and-outs of the code he wrote. However, in looking quickly at the code
he posted, the problem may lie with the line above the one you identified,
namely, this one...

While ActiveCell.Text < "Delete"

I would note that your original post said you had "DELETE" (all upper case)
in your cells whereas Wouter's code is testing against "Delete" (mixed
case). Try changing this line of code to the following and see if that makes
your code work...

While ActiveCell.Text < "DELETE"

Rick Rothstein (MVP - Excel)

Hi Dan,

To make it even beter try this:

While UCase(ActiveCell.Text) < "DELETE"

If you change the formula in the A column to lower or mixes case the
macro will still work.

Wouter

On Mar 9, 10:06*am, "Rick Rothstein"
wrote:
I can't seem to get the macro to work. I am using the
combined macro provided by Wouter. It appears the
error is in the "ActiveCell.Offset(1,0).Select" line. At
least that is what is highlighted when I do debug. Any
thoughts on what I could be missing or doing wrong?

Since you are using Wouter's code, I would think you should be directing
your question to him, not me, given that he would be more familiar with the
ins-and-outs of the code he wrote. However, in looking quickly at the code
he posted, the problem may lie with the line above the one you identified,
namely, this one...

While ActiveCell.Text < "Delete"

I would note that your original post said you had "DELETE" (all upper case)
in your cells whereas Wouter's code is testing against "Delete" (mixed
case). Try changing this line of code to the following and see if that makes
your code work...

While ActiveCell.Text < "DELETE"

Rick Rothstein (MVP - Excel)

I changed the word Delete to all caps and it seemed to work. However,
when it completes, the message box says "0 milliseconds" and it still
appears to run until I hit OK. Is this what it is supposed to do?
Overall it took about 5 minutes to delete the rows on 8 tabs.

On a side note, what is interesting is that the time it takes do
delete the rows is inverse to the amount of "DELETE" rows I have. For
instance, the spreadsheet I scale down with the most "DELETE"S, has
about 7,750 "DELETE"'s. (I am only keeping 250 rows). It takes about
25 seconds to cycle through. The spreadsheet with the least amount of
deletes (4,500 rows to Delete), it takes about 5 minutes. It just
seems like the more rows it has to delete, the longer it should take.
Just an observation.

Thank you again for your help!