CODEWITHSUNDEEP
excelvba
Louisa Thompson
Louisa Thompson

Reputation: 97

Attempting to filter large amounts of data and delete rows

I have a large table of info in excel. What I am trying to do is filter it and delete certain rows (where total is blank in a column).

The code works it just takes forever. There are over 30k rows in the table.

The total is a figure generated by VBA and not a formula so that isn't slowing it down. I have Application.ScreenUpdating = False when I run it so that should help.

I am not sure how to make it run faster. I would have thought a loop may be even slower but happy to try some different methods.

Application.ScreenUpdating = False

lngLastRow = Worksheets("report").Cells(Rows.Count, "b").End(xlUp).Row
Set rngFilter = Worksheets("report").Range("B7:n" & lngLastRow)
rngFilter.AutoFilter Field:=13, Criteria1:="0.00"

With ActiveSheet.AutoFilter.Range
    On Error Resume Next
    Set rng2 = .Resize(.Rows.Count - 1, 1) _
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
End With

ActiveSheet.ShowAllData

Application.ScreenUpdating = True

Upvotes: 0

Views: 3628

Answers (1)

SickDimension
SickDimension

Reputation: 912

You could speed up it a bit if it contains lots of calculted cells by turning the formula calculation off for the time the code is excecuted

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.EnableCalculation = False
Next

And of course turn it back on after the code is excecuted

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.EnableCalculation = True
Next

Upvotes: 1

Related Questions