Speed up my VB code a little ?

[Pauljj]

I have the following code simply to delete certain rows based on certain conditions. it all works according to plan, however when I have about 2,000 rows, in can take a couple of minutes. I am sure this is down to the way I have written the code. Can anyone see from the below a quicker way of achieving this please ?

----------------------------------------------------------------------------
For I = finalrow To 3 Step -1

'SOURCE CODE REMOVAL

If Cells(I, 13) = "AST" Or Cells(I, 13) = "SST" Then

Range(Cells(I, 2), Cells(I, 14)).Delete

End If

'PAGE SPLIT HEADERS REMOVAL

If Left(Cells(I, 2), 4) = "DATE" Or Left(Cells(I, 2), 6) = "TF BKG" Then

Range(Cells(I, 2), Cells(I, 14)).Delete

End If

'SUPPLIER CODE REMOVAL

If Cells(I, 9) = "C2A" Then

Range(Cells(I, 2), Cells(I, 14)).Delete

End If

'TOUR CODE REMOVAL

If Cells(I, 14) = "WEDING" Then

Range(Cells(I, 2), Cells(I, 14)).Delete

End If



Next I

--------------------------------------------------------------------------

Thanks

 

[RichardMGreen]

If you have formulas on the sheet, add application.calculation=xlmanual at the beginning of the code and application.calculation=xlautomatic at the end.

 

[Pauljj]

I only have a few formulas, so whilst it has sped it up a little, it's not made a massive difference

 

[RichardMGreen]

You could also try putting all your IF statements together on one line as they all seem to be doing the same thing.
You may want to group them in brackets to separate them if necessary.

 

[RoryA]

Try this:

   Dim rngDel            As Range
   
   For I = finalrow To 3 Step -1

      If Cells(I, "M") = "AST" Or Cells(I, "M") = "SST" _
         Or Left(Cells(I, "B"), 4) = "DATE" Or Left(Cells(I, "B"), 6) = "TF BKG" _
         Or Cells(I, "I") = "C2A" _
         Or Cells(I, "N") = "WEDING" Then

         If rngDel Is Nothing Then
            Set rngDel = Range(Cells(I, 2), Cells(I, 14))
         Else
            Set rngDel = Union(rngDel, Range(Cells(I, 2), Cells(I, 14)))
         End If

      End If
   Next I
   If Not rngDel Is Nothing Then rngDel.Delete

 

[Pauljj]

Richard/Rory thank you both very much, I shall try that now


Regards

Paul