Help with Speeding up the .Cell Formula??

[elephant97]

I'm running a FOR,NEXT loop that is just plain slow. What options do I have to speed up the code written below so that I'm not waiting forever for it to run? THanks

With Worksheets("Sheet1")
For rw = .Cells(10000, 1).End(xlUp).Row To 2 Step -1

If .Cells(rw, 1).Value = "BS " Then .Cells(rw, 1).EntireRow.Delete

If .Cells(rw, 1).Value = "S1 " Then .Cells(rw, 1).EntireRow.Delete

If .Cells(rw, 1).Value = "S2 " Then .Cells(rw, 1).EntireRow.Delete

If .Cells(rw, 1).Value = "S3 " Then .Cells(rw, 1).EntireRow.Delete

If .Cells(rw, 2).Value = 108 Then .Cells(rw, 2).EntireRow.Delete


If .Cells(rw, 2).Value = 901 And .Cells(rw, .Value > 24 And .Cells(rw, .Value > 48 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, 2).Value = 902 And .Cells(rw, .Value > 24 And .Cells(rw, .Value > 48 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, 2).Value = 903 And .Cells(rw, .Value > 24 And .Cells(rw, .Value > 48 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, 2).Value = 904 And .Cells(rw, .Value > 24 And .Cells(rw, .Value > 48 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, 2).Value = 905 And .Cells(rw, .Value > 24 And .Cells(rw, .Value > 48 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, 2).Value = 906 And .Cells(rw, .Value > 24 And .Cells(rw, .Value > 48 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, 2).Value = 912 And .Cells(rw, .Value > 24 And .Cells(rw, .Value > 48 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, 2).Value = 913 And .Cells(rw, .Value > 24 And .Cells(rw, .Value > 48 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, .Value > 24 And .Cells(rw, .Value > 100 Then .Cells(rw, .EntireRow.Delete

If .Cells(rw, 13).Value <> "ST" Then .Cells(rw, 13).EntireRow.Delete


99 Next
End With


I'm New, please be easy Thanks!!!

 

[Damon Ostrander]

Hi elephant,

Your code could be made more efficient by use of ElseIf statements rather than multiple If statements. But probably the biggest improvement in speed would result from simply turning off screen updating while it is processing. To do this simply set

Application.ScreenUpdating = False

at the beginning of the macro and

Application.ScreenUpdating = True

at the end. The improvement results from the fact that each time a row is deleted quite a bit of time is required to update the screen.

 

[elephant97]

THanks, that definately helped out, but I've only got 1200 rows of information. Should it really take over one minute to run this set of statments?

 

[Damon Ostrander]

Hi elephant,

It shouldn't take anywhere near a minute with screen updating off, but with it on times on the order of minutes occur frequently when the sheet structure is changed because some types of structure changes animate (row deletion is an example of this). This animation makes it easier when adding or deleting rows manually to see what is going on, but when making lots of changes, as in your case, the animation simply slows things down.

Damon