Excel VBA is slow

[webrabbit]

The following code runs very slow with a 3-second pause after the "end if", before the "next row". Does anyone have any ideas on how to speed it up?

Sub TryThis()
    Application.ScreenUpdating = False
'   Paste Data
    Range("C1").Select
    ActiveSheet.Paste
'   Parse to item level records
    totalrows = ActiveSheet.UsedRange.Rows.Count
    Range(Cells(21, 1), Cells(totalrows, 1)).FormulaR1C1 = _
        "=IF(OR(RIGHT(RC[2],8)=""Standard"",RIGHT(RC[2],13)=""Expense Repor"",RIGHT(RC[2],11)=""Credit Memo"",RIGHT(RC[2],10)=""Prepayment"",RIGHT(RC[2],10)=""Debit Memo""),RC[2],R[-1]C)"
    Columns("A:A").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'   Remove Headers
    totalrows = ActiveSheet.UsedRange.Rows.Count
    Range(Cells(1, 2), Cells(totalrows, 2)).FormulaR1C1 = "=IF(OR(LEFT(RC[1],6)=""  Item"",LEFT(RC[1],6)=""  Misc"",LEFT(RC[1],6)=""  Frei"",LEFT(RC[1],6)=""  Prep"",LEFT(RC[1],5)=""  Tax""),"""",""Delete"")"
    Columns("B:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    For Row = totalrows To 1 Step -1
        If Cells(Row, 2).Value = "Delete" Then
            Rows(Row).Delete
        End If
    Next Row
    Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Hi,

Please post VBA questions in Forum707.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

basically I'd suggest instead of using a loop to delete,

apply an AutoFilter
Select Delete as that column's criteria
Delete the visible rows all at once.

Again, post back in Forum707.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[webrabbit]

Thanks, Skip. I didn't realize that was a separate forum.

Problem resolved though. Another (very complex) workbook was open, causing recalulations on each "for" cycle. Solutions are: quickest, close the other workbook; more generat, block recalculations during loop; or as you say, apply an AutoFilter instead.