title: Increasingly Long Runtime for Macro
body: <p>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 sytax, but as I'm pretty new to VBA, I havn't made a whole lot of progress. Here's the code I'm running (Note, it is running as a subset, and <code>ScreenUpdate = False</code>):</p>
  
  <pre><code>Public Sub deleteRows()
  
      Dim lastRow As Long
      Dim rng As Range
      With ActiveSheet
          .AutoFilterMode = False
          lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
          '~~&gt; Set the range of interest, no need to include the entire data range
              With .Range("B2:F" &amp; lastRow)
                  .AutoFilter Field:=2, Criteria1:="=0.000", Operator:=xlFilterValues
                  .AutoFilter Field:=5, Criteria1:="=0.000", Operator:=xlFilterValues
              End With
          .Range("B1:F" &amp; 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
  </code></pre>
  
  <p>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 recored runtimes and corresponding changes made:</p>
  
  <pre><code>Runtime Changes
  6:30    None
  7:50    None
  5:37    Manually stepped through code
  7:45    Run with .cells instead of .range("B1:B" &amp; lastRow)
  5:21    Run with .Range(B:B)  instead of .range("B1:B" &amp; 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" &amp; 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
  </code></pre>
  
  <p>I did some reasearh 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." Source: <a href="http://support.microsoft.com/kb/199505" rel="nofollow">http://support.microsoft.com/kb/199505</a></p>
  
  <p>So my question is: Is there any way to make this run faster, like it initially did? Why is this happening?</p>