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:

sub delete_sunday

for x = 1 to cells(rows.count,"a").end(xlup).row

  If weekday(cells(x,"A").value) = 7 then
    rows(x).delete xlshiftup
    x= x -1 
  end if 

next x

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

Sub test()
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Value = Evaluate("if(weekday(" & .Address & ")=7,""""," & .Address & ")")
        .SpecialCells(4).Delete xlShiftUp
    End With
End Sub

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

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.

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

Sub test()
    Application.Calculation = xlCalculationManual
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Value = Evaluate("if(weekday(" & .Address & ")=7,""""," & .Address & ")")
        .SpecialCells(4).Delete xlShiftUp
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

that is even faster, Thanks !!