Help with Speeding up the .Cell Formula??

elephant97

Board Regular
Joined
Sep 18, 2002
Messages
63
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, :cool:.Value > 24 And .Cells(rw, :cool:.Value > 48 Then .Cells(rw, :cool:.EntireRow.Delete

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

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

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

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

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

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

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

If .Cells(rw, :cool:.Value > 24 And .Cells(rw, :cool:.Value > 100 Then .Cells(rw, :cool:.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!!!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Sep 18, 2002
Messages
63
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,144,274
Messages
5,723,444
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top