Is there a faster way than a loop to delete rows that satisfy a criteria

Hi everyone,

I am deleting every sunday from a 5- year dates list.

My list of date is in column "A".

Since Sunday = weeday("date") -> 7

I do the following:

Please Login or Register  to view this content.

This works, but it takes a while, do you know another way to delete things within a range that is quicker?

Thanks for your time,

-Phil

You could turn your screenupdating off.

Application.screenupdating = false

YOUR CODE

Application.screenupdating = true

7 for Sunday?
1 for Sunday to me...

Please Login or Register  to view this content.

Thanks guys.

I put a timer on my code and here is my results:
On a total of 1,304 dates it tooks
1) updatescreen turn on = 01:56
2) updatescreen turn off = 01:46

Ps my computer is not very fast, though!

Sorry
Jindon: you are right sunday = 1 not 7. let me try your solution.

Jindon: your solution took 40 seconds. that is awesome.


now, I need to understand it.

1) I get the first part With "a range"
2) the second line I don't entirely understand it. I have never used EVALUATE
3) then you cleared any empty cell within the range using specialcells(4).delete

thanks a lot for your solutions guys
-Phil

40 Sec?

How many rows do you have?

I tested with 4000 rows and took only 0.5 sec.

only a 1000 rows, let me try that again. I will first kill any other software and repopen excel.

by the way, I am impress this evaluate method I need to get familiar with it

Try add two lines

Please Login or Register  to view this content.

that is even faster, Thanks !!