Efficiency - DELETE ROWS

[emukiss10]

Hello Guys!

Help me speed up my code please.

I need to remove rows that contains specific pair of values in columns L and M.
80 and 1, 80 and 01, 80 and 03, 80 and 10 (01 and 1 are different - its all TEXT)

ex.

[TABLE="width: 500"]
<tbody>[TR]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My working code is :

    Dim Fil As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    Fil = Range("A" & Rows.count).End(xlUp).row
        With Range("A1:N" & Fil)
          .AutoFilter
          .AutoFilter field:=12, Criteria1:="80"
          .AutoFilter field:=13, Criteria1:=Array( _
                "01", "03", "1", "10"), Operator:=xlFilterValues
          .Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Delete
        End With
    Selection.AutoFilter
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

This code works fine but the trick is that I have over 1.000.000 records and can it be done faster with better code??

Best Regards
W.

 

[JLGWhiz]

just on a rough calculation, with over a 1 meg in rows, the best time would be appx 5 seconds. If you are beating that, then you are pretty much optimal.

 

[emukiss10]

Thank You JLGWhiz

660.467 rows populated.
Time: 46.28 sec.

mayby filering is not the best way to work on big sheets?

Any sugestions?

 

[Fazza]

commonly for deleting entire rows of visible cells with large datasets the slowness is overcome if the data is sorted

if it can be done, sort on columns L & M to create just a few contiguous blocks to delete - instead of perhaps thousands without sorting

resort to a different (starting) order after doing the deleting, if required

 

[emukiss10]

...

By the Mother of Bill G... it was that simple...
Time: 0.4 sec ..

THX!

 

[Fazza]

good work