[VBA] Very slow looping through deleting rows

[ajamess]

I'm writing a macro to prompt for a user entered category and loop through ~ 40k rows and delete all except their category (with the largest category this would be ~8k left, so deleting 32k rows). I'm thinking there has to be a more efficient way to do this, its been running at least 7 minutes (have a timer, running, i'll post when complete)



Sub category()

Workbooks("Book1.xlsx").activate

dim category as variant

category = InputBox("What Category are you analyzing?")

Rowcount = application.worksheetfunction.countA(Range("A:A"))

For i =RowCount to 2 Step -1
If cells(i,3).value <> category Then
Rows(i).delete
End If
Next



It ended up taking 18 minutes. There has to be a way to speed this up!

 

[Michael M]

Try

Sub MM1()
Workbooks("Book1.xlsx").Activate
Application.ScreenUpdating = False
Dim lr As Long, category As Variant 'could be dimmed as string if it is text
lr = Cells(Rows.Count, "A").End(xlUp).Row
category = InputBox("What Category are you analyzing?")
For i = lr To 2 Step -1
    If Cells(i, 3).Value <> category Then
        Rows(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Try this:

Sub FilterMini()
Application.ScreenUpdating = False
Workbooks("Book1.xlsx").Activate
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Dim category As Variant
category = InputBox("What Category are you analyzing?")
    With ActiveSheet.Range("C1:C" & Lastrow)
        .AutoFilter Field:=1, Criteria1:=category, Operator:=xlFilterValues
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[Michael M]

@MAIT
Agreed your method will be quicker....but I'm concerned that the filter limit of 10000 uniques might come into play, especially if it's an older version of EXcel..

 

[My Aswer Is This]

@MAIT
Agreed your method will be quicker....but I'm concerned that the filter limit of 10000 uniques might come into play, especially if it's an older version of EXcel..

I did not know about limits:

And now that I look again it should be <> like this:

.AutoFilter Field:=1, Criteria1:="<>" & category, Operator:=xlFilterValues