VBA Worksheet Function : CountIf


           Two columns in different worksheets were compared in this template.That is, column A of Page1 compared with column A of Page2.
The found different results as entire row were copied to second worksheet (Page2). 

✔️ Also new row or rows were highlighted .

excel vba compare two columns

Our VBA codes:
Sub compare_columns()
Dim stk, msb As Worksheet
Set stk = Sheets("Page1")
Set msb = Sheets("Page2")

Application.ScreenUpdating = False
sat = (msb.Range("A" & Rows.Count).End(xlUp).Row) + 1
For i = 2 To stk.Range("A" & Rows.Count).End(xlUp).Row
    If WorksheetFunction.CountIf(msb.Range("A2:A" & msb.Range("A" & Rows.Count).End(xlUp).Row), stk.Cells(i, "A")) = 0 Then
        msb.Range("a" & sat).EntireRow.Value = stk.Range("a" & i).EntireRow.Value
        msb.Range("a" & sat).Interior.ColorIndex = 22
        sat = sat + 1
    End If
Next
Application.ScreenUpdating = True
Set stk = Nothing: Set msb = Nothing
End Sub


2 Comments

  1. Hi. I try the VBA codes. so far, after it detect column not same, then it just copy 1 row only. not copying below row of it. please show how to loop until end of data.

    ReplyDelete
  2. This can be helpful for consolidating data from multiple sources.

    ReplyDelete

Post a Comment

Previous Post Next Post