title: Fast compare method of 2 columns
body: <p><strong>EDIT:</strong> Instead for my solution, use something like</p>
  
  <pre><code> For i = 1 To tmpRngSrcMax
       If rngSrc(i) &lt;&gt; rngDes(i) Then ...
   Next i
  </code></pre>
  
  <p>It is about 100 times faster.</p>
  
  <p>I have to compare two columns containing string data using VBA. This is my approach:</p>
  
  <pre><code>Set rngDes = wsDes.Range("A2:A" &amp; wsDes.Cells(Rows.Count, 1).End(xlUp).Row)
  Set rngSrc = wsSrc.Range("I3:I" &amp; wsSrc.Cells(Rows.Count, 1).End(xlUp).Row)
  
  tmpRngSrcMax = wsSrc.Cells(Rows.Count, 1).End(xlUp).Row
  cntNewItems = 0
  
  For Each x In rngSrc
  
  tmpFound = Application.WorksheetFunction.CountIf(rngDes, x.Row)
  Application.StatusBar = "Processed: " &amp; x.Row &amp; " of " &amp; tmpRngSrcMax &amp; " / " &amp; Format(x.Row / tmpRngSrcMax, "Percent")
  DoEvents ' keeps Excel away from the "Not responding" state
  
  If tmpFound = 0 Then ' new item
      cntNewItems = cntNewItems + 1
  
      tmpLastRow = wsDes.Cells(Rows.Count, 1).End(xlUp).Row + 1  ' first empty row on target sheet
      wsDes.Cells(tmpLastRow, 1) = wsSrc.Cells(x.Row, 9)
  End If
  Next x
  </code></pre>
  
  <p>So, I'm using a For Each loop to iterate trough the 1st (src) column, and the CountIf method to check if the item is already present in the 2nd (des) column. If not, copy to the end of the 1st (src) column. </p>
  
  <p>The code works, but on my machine it takes ~200s given columns with around 7000 rows. I noticed that CountIf works way faster when used directly as a formula.</p>
  
  <p>Does anyone has ideas for code optimization?</p>