Compare Two Columns In Different Worksheets & Add Found Results

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



Read more ...

Excel VBA Find And Delete

Excel Find & Delete Macro


           In this sheet, the value that you want to delete is found with macro.

With the opened msgbox ,found value's address is reported ,and you are asked whether you want to delete.
If you click "Yes" button on the msgbox, the row that contain value is deleted as whole.

Xlpart was selected  as the search method of "Find Method":

Set bul = Range(Cells(3, 1), Cells(Cells(Rows.Count, 1)._
End(xlUp).Row, LastColumn)).Find(WhatToFind, LookAt:=xlPart)

The macro searchs the value since the third row, because first row is empty and second row is header row.



Read more ...