Deleting via autofilter takes too long

I have roughly 8000+ rows. Using autofilter to delete rows takes a few minutes. I thought autofilter was the defacto FAST way to delete (instead of looping row by row). How can I speed it up? Is there a faster way? To be fair, half of the rows are deleted XD

With ThisWorkbook.Worksheets("Upload")
    lastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
    Set dataRng = .Range(.Cells(4, 1), .Cells(lastRow, 19))
    dataRng.AutoFilter field:=19, Criteria1:="=0"
    Application.DisplayAlerts = False
    dataRng.Offset(1, 0).Resize(dataRng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
    Application.DisplayAlerts = True
    .ShowAllData
End With

Answers


I'll challange to underlying assumption that AutoFilter is the fast way to go - it is often hard to beat a loop over a variant array

This demo shows a way to do this, on my system processing 8000+ rows removing half runs in sub-second

Sub DEMO()
    Dim datrng As Range
    Dim dat, newdat
    Dim i As Long, j As Long, k As Long
    With ThisWorkbook.Worksheets("Upload")
        Set datrng = .Range(.Cells(1, 1), .Cells(.Rows.Count, "S").End(xlUp))
    End With
    dat = datrng.Value
    ReDim newdat(1 To UBound(dat, 1), 1 To UBound(dat, 2))
    j = 1
    For i = 1 To UBound(dat, 1)
        If dat(i, 19) <> 0 Then ' test for items you want to keep
            For k = 1 To UBound(dat, 2)
                newdat(j, k) = dat(i, k)
            Next
            j = j + 1
        End If
    Next

    datrng = newdat
End Sub

I tested the macros for speed and found that sorting, autofiltering and deleting is faster then building an array.

Using the timing code here I run the original code over 100k lines of random data (25 columns wide random numbers between 0-4).

-Original code took 78 seconds (only ran 50k rows here to speed it up)

-The array code presented by chris took 1.91 seconds

-The code below took 0.84 seconds (Tried running it sorting ascending and descending and it made little difference if the range of zeros was sorted to the top or the bottom.

I realize the built in clock isn't great in vba but the difference is enough that I am comfortable saying sorting,filtering, deleting is at least as fast as arrays in this case.

The code below just added dataRng.Sort key1:=Range("S4"), order1:=xlDescending, Header:=xlYes to the original code

Sub test()

With Sheets("sheet1")
    lastRow = .Range("S" & .Rows.Count).End(xlUp).Row
    Set dataRng = .Range(.Cells(4, 1), .Cells(lastRow, 25))
    dataRng.Sort key1:=Range("S4"), order1:=xlDescending, Header:=xlYes
    dataRng.AutoFilter field:=19, Criteria1:="=0"
    Application.DisplayAlerts = False
    dataRng.Offset(1, 0).Resize(dataRng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
    Application.DisplayAlerts = True
    .ShowAllData
End With
End Sub

Need Your Help

SetTwo Class Path For execution of Java Program

java java-ee servlets web-applications

I want to Link Two(2)(i.e. for eg: servlet-api.jar and kvclient.jar) .jar file to my program in java through command Prompt.

How should I generate documentation for my web service API?

c# asp.net .net web-services documentation

I am exposing my API through web service (.asmx). Currently, the only thing available is the default WSDL help page. While this is immensely helpful, it is not sufficient if my target audience req...