excel - VBA macro script : Find and copy unique values within a column in sheet 1 to sheet 2 using vba macro

Question

I have 2 sheets within the same workbook. In worksheet A called "sheet1" and worksheet B called "sheet2". From column A of sheet 1 there are upto 176080 records of duplicate ID numbers. Need to find the unique ID numbers from this column and paste it into column A of sheet 2.

Any help would be appreciated, I am new to VBA macro and found some codes online but do not understand it. Please help me and kindly provide a syntax to solve this with some explanation so I could learn how to do it on my own as well. Thanks!!

Answer 1

May be a little complicated, but this gives back the unique numbers in column "A".

Option Explicit
Dim i, j, count, lastrow As Integer
Dim number As Long

Sub find_unique()
    lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    For i = 1 To lastrow
        number = Cells(i, 1)
        For j = 1 To lastrow
            If number = Cells(j, 1) Then
                count = count + 1
            End If
        Next j
    If count = 1 Then
        Cells(i, 5) = number
    Else
        Cells(i, 5) = ""
    End If
    count = 0
    Next i
End Sub

First the sub takes cell A1 then loops through all other cells, starting at the first, to the last cell in the active Sheet. If a number is equal to more than one cell (it's allways one, because u also check the cell with it's own value) the number will not be displayed in column E. Then it takes the next number and loops through all again until every number is checked. Small changes and the numbers will be shown in the other sheet. Hope it works for you.