My code works, but the problem is that it is taking an increasingly long time to run, with the time required to complete calculations increasing every time I use the macro. I've tried a variety of variations and modifications with the syntax, but as I'm pretty new to VBA, I haven't made a whole lot of progress.
Here's the code I'm running (Note, it is running as a subset, and ScreenUpdate = False
):
Public Sub deleteRows()
Dim lastRow As Long
Dim rng As Range
With ActiveSheet
.AutoFilterMode = False
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
'~~> Set the range of interest, no need to include the entire data range
With .Range("B2:F" & lastRow)
.AutoFilter Field:=2, Criteria1:="=0.000", Operator:=xlFilterValues
.AutoFilter Field:=5, Criteria1:="=0.000", Operator:=xlFilterValues
End With
.Range("B1:F" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
MsgBox Format(Time - start, "hh:mm:ss")
End Sub
This code basically removes zero-valued results from the data by deleting an entire row. Initially, it ran in about 12 seconds, but that soon became 55 second, which has progressed into increasing long runtimes, with a 'fast' now being in the 5 minuet range. Below is a spreadsheet with the recorded runtimes and corresponding changes made:
Runtime Changes 6:30 None 7:50 None 5:37 Manually stepped through code 7:45 Run with .cells instead of .range("B1:B" & lastRow) 5:21 Run with .Range(B:B) instead of .range("B1:B" & lastRow) 9:20 Run with application.calculation disabled/enabled, range unchanged 5:35 Run with application.enableEvents disabled/enabled, range unchanged 11:08 Run with application.enableEvents disabled/enabled, Range(B:B) 5:12 None 7:57 Run with Alternative code (old code) 5:45 Range changed to .Range(cells(2,2), Cells(lastRow,2) 10:25 Range changed to .Range(cells(2,2), Cells(lastRow,2), Application.Calculation Disabled/enabled 5:34 Range set to rngB for .delete portion (range assigned to variable) 9:59 Range set as rng("B1:F" & lastRow) 5:58 Changed system settings for Excel to "High Priority", code reverted to original 9:41 Rerun of old code for comparison 9:26 Reun with change in old code criteria to "0.000" 0:10 Moved SpecialCells……..Delete into 2nd With/End With 5:15 Rerun SpecialCells……..Delete into 2nd With/End With 11:31 Rerun SpecialCells……..Delete into 2nd With/End With 11:38 Excel restart; Rerun SpecialCells……..Delete into 2nd With/End With 5:18 Excel restart; Rerun SpecialCells……..Delete into 2nd With/End With 6:49 Removed 2nd with 'loop'; all data put into first with statement
I did some research online, and it looks like this might be a known issue with Excel when working with large datasets, and as mine is ~51k rows, I can see how this might be the case.
...A macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel. Alternatively, if you run a macro a second time, the macro may take twice as long to run as it did the first time.
Is there any way to make this run faster, like it initially did? Why is this happening?