title: Speeding up run time in VBA excel macro with double loop
body: <p>I actually have some working code, although with the amount of data I have and the way I wrote the code, it takes upwards of an hour to run, and I still need to add quite a bit of code to actually analyze the data. I'm using a double loop, and before I added the screenupdating = false it seemed like the loop nested on the inside was what was taking so long.</p>
  
  <p>Here's what I have:</p>
  
  <pre><code>Sub LReview()
  
   Dim SecX As Workbook, LipR As Workbook
   Dim ws As Worksheet, Xws As Worksheet, Fsheet As Worksheet
   Dim i As Long, XwsRows As Long
  
   Path = ThisWorkbook.Path &amp; "\"
  
  Set LipR = ThisWorkbook
  Set SecX = Application.Workbooks.Open(Path &amp; "SecurityXtract_Mnthly.csv")
  Windows("SecurityXtract_Mnthly.CSV").Activate
  Set Xws = Sheets("SecurityXtract_Mnthly")
  
  With Xws
      XwsRows = .Range("B" &amp; .Rows.Count).End(xlUp).Row
  End With
  
  Windows("LMacro.xlsm").Activate
  Sheets.Add.Name = "Funds"
  Set ws = Sheets("Funds")
  
      Windows("SecurityXtract_Mnthly.CSV").Activate
          Columns("B:B").Select
      Selection.Copy
      Windows("LMacro.xlsm").Activate
      ws.Select
      Range("A1").Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
      ActiveSheet.Range("$A$1:$A$60000").RemoveDuplicates Columns:=1, Header:= _
          xlNo
  
      Application.DisplayAlerts = False
      Application.ScreenUpdating = False
  
      With ws
          'Change back to 100+
          For i = 2 To 5
  
  
              If ws.Range("A" &amp; i).Value &lt;&gt; "" Then
              Sheets.Add(After:=Sheets(Worksheets.Count)).Name = ws.Range("A" &amp; i).Value
              Set Fsheet = ActiveSheet
              Range("A1").Value = "Fund:"
              Range("B1").Value = Fsheet.Name
              Range("A2").Value = "Date:"
              Range("B2").Value = "=Xtract!R[-1]C"
  
              Windows("SecurityXtract_Mnthly.CSV").Activate
              Rows("1:1").Select
              Selection.Copy
              Windows("LMacro.xlsm").Activate
              Rows("4:4").Select
              ActiveSheet.Paste
              Selection.Font.Bold = True
              Application.CutCopyMode = False
  
              For j = 2 To XwsRows
                  If Xws.Range("B" &amp; j).Value = Fsheet.Range("B1") Then
                      Windows("SecurityXtract_Mnthly.CSV").Activate
                      Xws.Range("B" &amp; j).Select
                      ActiveCell.EntireRow.Select
                      Selection.Copy
                      Windows("LMacro.xlsm").Activate
                      Fsheet.Range("A" &amp; j + 3).EntireRow.Select
                      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                      :=False, Transpose:=False
                      Application.CutCopyMode = False
                      Columns("A:A").Select
                      Selection.SpecialCells(xlCellTypeBlanks).Select
                      Selection.EntireRow.Delete
  
                  End If
              Next j
  
              Range("C:D, F:F, I:BB, BD:BL, BP:BR, BT:BV, BX:CD, CF:CN, CP:DI").EntireColumn.Select
                      Selection.Delete Shift:=xlToLeft
  
              End If
  
                  Cells.Select
                  Cells.EntireColumn.AutoFit
                  Range("A1").Select
  
  
          Next i
      End With
  
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
  
  
   End Sub
  </code></pre>
  
  <p>I also found this code on another question, but I'm not sure if it can be applied since I'm using two different workbooks. This code:</p>
  
  <pre><code> If Range("S1").Offset(i) &gt; 0.005 Then
              Range("AC").Offset(i).Resize(1, 2).Value = Range("Z").Offset(i).Resize(1, 2).Value
      End If
  </code></pre>
  
  <p>Replaced this:</p>
  
  <pre><code>If Range("S" &amp; i) &gt; 0.005 Then
          Range("Z" &amp; i, "AA" &amp; i).Copy
          Range("AC" &amp; i, "AD" &amp; i).PasteSpecial xlPasteValues
  End If
  </code></pre>
  
  <p>The full link of this code/question I reference here is:
  <a href="https://stackoverflow.com/questions/14126578/suggestions-on-how-to-speed-up-loop">Suggestions on how to speed up loop</a></p>
  
  <p>Thanks in advance for any help you can give :)</p>