What is wrong with this formula?

Besides the fact that I'm stupid.

I'm trying to delete all rows in which "K" column value is less then 30.

Sub Delete()
For lRow = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If Value(Cells(lRow, "K")) < 30 Then
Rows(lRow).DELETE
End If
Next lRow

End Sub

Also, why some macros are so slow?
I have about 3000 rows in my file and only want to see data from today (about only 100 rows), so I use same formula to delete all that is not from today (it works, but very slow and I get an error at the end, but it still works):
BUT IT IS SOOOOOOO SLOW. Anybody knows why? Is there a better way?

Sub onlycurrent()

For lRow = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If Value(Cells(lRow, "M")) < DateValue(Now()) Then
Rows(lRow).DELETE
End If
Next lRow

End Sub

Hello Zolo33,

In cases like this, where you have a lot of data, it is much quicker to load the data you wish to keep into an array, clear the column, and copy the array values back into the column. When you delete a row, Excel has to do a lot of shifting and checking each time. Clearing a column of values once is much quicker.

Other things you can do to speed up loop execution are declare the variable type you need. VBA defaults to a variant which requires more memory and is slower to access. Don't calculate a value over and over in the loop if it doesn't change. You call Now in your loop. Each time Now is encountered, it calculates the Date and Time. You are really only interested in the Date. Place Now in variable and compare the variable's value to the cell value you are testing.
_________________________________________________________________

Sub Delete()

Dim MyArray(65536)

Dim I As long
Dim N As Long
Dim Col As Long
Dim lrow As Long
Dim MyVal As Long

'Translate "K" into a number, and Get the Last Row Used in Column "K"
Col = Range("K:K").Column
lRow = Cells(Rows.Count, Col).End(xlUp).Row

'Test the Values and Keep Only the Current Ones
For I = 1 To lRow
MyVal = Cells(I, Col).Value
If MyVal >= 30 Then
N = N + 1
MyArray(N) = MyVal
End If
Next I

'Clear Column "K"
Range("K1:K" & lRow).ClearContents

'Copy the Current Data Back into Column "K"
For I = 1 To N
Cells(I, Col).Value = MyArray(N)
Next I

End Sub_________________________________________________________________

You're probably a little skeptical that all this code will run faster, but it will. if you run into any problems, e-mail me at [email protected].

Sincerely,
Leith Ross