How to delete multiple rows fast based on multiple conditions in column A and B?

[VBA_Cancer]

So I got a code that deletes rows based on 2 column conditions. However, it's very slow.
I have about 60, 000 rows and this takes forever.

Sub ClearOldest()
    OldestWeek = 34
    OldestYear = 2018

    i = 2

    While Sheet5.Cells(i, "Q") = OldestWeek And Sheet5.Cells(i, "R") = OldestYear
   
        Sheet5.Rows(i).EntireRow.Delete
   
    i = i + 1
    Wend
   
    MsgBox "Mission Accomplished"
End Sub

My data looks something like this:

week	year	data1	data2
33	2018	1335	353
33	2018	2323	353
34	2018	35223	33533
34	2018	353	3535

Week number and year is sorted by oldest to newest.


Is there a much faster method?

Thanks

 

[alansidman]

Add at the beginning of your code

Application.ScreenUpdating=False

and add to last line before End Sub this line

Application.ScreenUpdating=True

and see if that speeds things up.

 

[VBA_Cancer]

I'm so retarded, I could've done this

Sub ClearOldest()
Application.ScreenUpdating = False

    OldestWeek = 34
    OldestYear = 2018
 
    i = 2
    While Sheet5.Cells(i, "Q") = OldestWeek And Sheet5.Cells(i, "R") = OldestYear
    i = i + 1
    Wend
    
    Sheet5.Rows("2:" & CStr(i)).EntireRow.Delete
Application.ScreenUpdating = False
    
    MsgBox "Mission Accomplished"
End Sub

I just find all the rows and delete them all at once. And your screen updating method helped too. Thanks

 

[alansidman]

Change the last line to True not False. You want it to repaint the screen after the Macro runs.

 

[VBA_Cancer]

You're right, let me edit it. Thanks