Compare 2 columns without using loop

Question

I have to compare 2 columns in 2 different sheets then add the missing cells in the second sheet. But the problem is that i used a loop and it takes too much time. Do someone have an idea of how to compare these 2 columns without using a loop.

    Set col_1 = Worksheets("Plan Traitement Risque").Range("B6:B700")

      With ThisWorkbook.Sheets("Analyse de risque")
        For i = 200 To 6 Step -1
          'Test si valeur cellule feuil1!=Ax est dans Plage col_2(feuil2!A1:A50))
        If Application.CountIf(col_1, .Range("B" & i).Value) = 0 Then
            'Delete ligne i feuil1
            .Rows(i).Copy
             Sheets("Plan Traitement Risque").Rows(i).Insert (xlShiftDown)
End If
    Next i
  End With

I avoid loops whenever I can, but you need it for comparing values. In your case the main problem is inserting rows, and CountIf(). You can improve the speed significantly if you copy both ranges to array, and write the second one back only once (it would helpful if you include the full function) — paul bica
– paul bica, Commented Sep 9, 2015 at 12:45

Balinti · Accepted Answer · 2015-09-09 12:43:27Z

1

Make a vertical lookup in excel first and then search for NAs in the loop. It will be much faster. You can delete the lookup after your work is done.

answered Sep 9, 2015 at 12:43

Balinti

1,5341 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare 2 columns without using loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related