VBA filter and delete

[ziad alsayed]

dear all

i am using th below code , and it is deleting about 19000 rows. the problem is that it is taking too long to delete them.

is there a way to make faster ?

sub try()
 Range("E1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$N$500000").AutoFilter Field:=5, Criteria1:="419", Operator:=xlFilterValues
    Range("A1").CurrentRegion.Offset(1, 0).Select
    Selection.EntireRow.Delete Shift:=xlUp
    Range("A1").Select
    ActiveSheet.Range("$A$1:$N$500000").AutoFilter Field:=5
     Range("E1").Select
    Selection.AutoFilter
    Columns("E:E").Delete
end sub

appreciate any help.

 

[jeffreybrown]

Untested, but try this on a copy of your workbook

Sub FilterDelete()
    Application.ScreenUpdating = False
    With Range("E1", Range("E" & Rows.Count).End(xlUp))
        .AutoFilter field:=1, Criteria1:="419"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Columns("E:E").Delete
    Application.ScreenUpdating = True
End Sub

 

[ziad alsayed]

dear jeffreybrown

thanks , but both code a taking about 2 minutes and 40 seconds to finish the task.

any other idea?

 

[jeffreybrown]

I just tested it on 500K rows and it ran in 3 seconds.

Do you have a lot of formulas in the worksheet?

If so try turning off calculations?

 

[ziad alsayed]

dear jeffreybrown

yes i do have a lot of formulas, but no array formula.

 

[jeffreybrown]

I don't think it matters if they are array formulas as much as volatile formulas.

http://msdn.microsoft.com/en-us/library/aa730921.aspx

http://www.decisionmodels.com/

Try and add

Application.Calculation = xlCalculationManual

to the beginning of the code and then at the end set it back to Automatic.

 

[ziad alsayed]

dear jeffreybrown

thanks for the assistance ad nfor the two links , i will check them.