CODEWITHSUNDEEP
vbaexcel
Souma
Souma

Reputation: 141

Compare 2 columns without using loop

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

Upvotes: 0

Views: 143

Answers (1)

Balinti
Balinti

Reputation: 1534

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.

Upvotes: 1

Related Questions