While loop macro taking much longer than expected

Hi I created a macro to delete rows in an excel sheet that I did not want. I'll have a worksheet with 30k+ rows in it but want to reduce it to only rows that have a specific value in column L. The macro is just a simple while loop but the runtime will be 40 mins or more and I'm not sure why. Does anyone know why this could take so long? Here is the VBA code:

Sub Delete_Ok_Rows()


Dim Counter As Long
Dim NumRows As Long
Dim RowCount As Integer

'Delete Points Classified as "OK"

NumRows = Cells(1, 17).Value
Counter = NumRows + 3


While Counter >= 5

'

If Cells(Counter, 11).Value = "OK" Then

Rows(Counter).EntireRow.Delete

End If

Counter = Counter - 1

Wend


'Delete Points Outside the Envelope

NumRows = Cells(1, 17).Value
Counter = NumRows + 3


While Counter >= 5

'

If Cells(Counter, 14).Value = "Yes" Then

Rows(Counter).EntireRow.Delete

End If

Counter = Counter - 1

Wend


End Sub

Hi there,

Without access to even a portion of your worksheet it's very difficult to see what's causing the delay in what appears to be simple looping code, but see if the following suggestion improves things at all:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Sorry I forgot to attach it. Here it is

Hi,

your VBA procedure is just the slowest way possible …

The kid easy & fast way is to use a helper column via a formula to mark the rows to keep or to delete via FALSE / TRUE or 0 / 1 whatever …
Then sort the range according to the helper column in order all the rows to delete are at the end of the range,
so easy & fast to clear the block at once rather than row-by-row !
Processing like this manually no needs more than a minute, so imagine how fast it can be just reproducing it under VBA …