Find unique value from 2 columns by VBA

[KIM Chanthan]

Hi,

I have run one formula with million rows, but it doesn't work as the data is too big.
So I am not sure if VBA can help. If so can help to give me code?
Please view attached file for more detail request.

Thank you very much for your kind support.
Chanthan
______________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[shrivallabha]

Right click on the Sheet tab and choose "View Code" option. Place this code there. It will fire automatically if something is changed in column A and B provided you have macros enabled.:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Long, i As Long
Dim varData, varOut, a
'\\ In case code fails
On erro GoTo EoSub

'\\ Test only if first two columns are changing
If Target.Column = 1 Or Target.Column = 2 Then
    Application.EnableEvents = False
    '\\ Last data row
    lastRow = Cells.Find("*", [A1], , , , xlPrevious).Row
    Range("C2:C" & lastRow).Delete xlUp
    '\\ Load info in array for fast processing
    varData = Range("A1:B" & lastRow).Value
    '\\ Using dictionary find uniques
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = LBound(varData) To UBound(varData)
            If IsNumeric(varData(i, 1)) And Len(varData(i, 1)) > 0 Then
                If Not .exists(varData(i, 1)) Then
                    .Add varData(i, 1), varData(i, 1)
                End If
            End If
            If IsNumeric(varData(i, 2)) And Len(varData(i, 2)) > 0 Then
                If Not .exists(varData(i, 2)) Then
                    .Add varData(i, 2), varData(i, 2)
                End If
            End If
        Next i
        a = .keys
        varOut = Range("C2").Resize(.Count, 1).Value
        For i = 0 To .Count - 1
            varOut(i + 1, 1) = a(i)
        Next
        '\\ Write to range
        Range("C2").Resize(.Count, 1).Value = varOut
    End With
End If

EoSub:
If Err.Number <> 0 Then
    MsgBox Err.Number & ": " & Err.Description & " was raised!"
End If
Application.EnableEvents = True
End Sub