To get unique numbers list with VBA

[Useful]

Hi mr excel!
Now i've question about macroses
i've list that starts from the range A3 to A9 (1,5,5,1,8,6,8)
and i want to get unique result from B3
i've created the macro that below but there is an error (I've the other macro code that extracts the unique numbers-but i want to find out what is wrong in this code?)

Sub Unique()
Dim a As Long
Dim b As Long
lastrow = Range("A10000").End(xlUp).Row
For a = 3 To lastrow
Range("B:B").Select
If Selection.Find(Cells(a, 1).Value) <> Cells(a, 1).Value Then
Cells(b, 2).Value = Cells(a, 1).Value
b = b + 1
End If
Next a
End Sub

Thanks beforehand!

 

[Sektor]

Sub D()

    Dim sh As Worksheet
    
    Application.DisplayAlerts = True
    
    Set sh = Sheets.Add
    Range("A3:A9").Copy
    sh.Range("A1").PasteSpecial , , , True
    sh.Columns("A:A").RemoveDuplicates
    Range("A3:A9").ClearContents
    sh.Range("A1").CurrentRegion.Copy
    Range("A3").PasteSpecial , , , True
    sh.Delete
    
    Application.DisplayAlerts = False
    
End Sub

 

[Useful]

Sub D()

    Dim sh As Worksheet
    
    Application.DisplayAlerts = True
    
    Set sh = Sheets.Add
    Range("A3:A9").Copy
    sh.Range("A1").PasteSpecial , , , True
    sh.Columns("A:A").RemoveDuplicates
    Range("A3:A9").ClearContents
    sh.Range("A1").CurrentRegion.Copy
    Range("A3").PasteSpecial , , , True
    sh.Delete
    
    Application.DisplayAlerts = False
    
End Sub

Thanks for your answer (this code isn't the very code i've asked)
but i want to know thar what is wrong in my code?

 

[Useful]

an error appears on this code

If Selection.Find(Cells(a, 1).Value) <> Cells(a, 1).Value Then

</pre>

 

[ayazgreat]

Try this

Sub AyazTest()
Dim a, i As Long, b(), n As Long, z As String
Application.ScreenUpdating = 0
a = Sheets("Sheet1").Range("A3").CurrentRegion.Resize(, 1).Value
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 1))
    
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 1 To UBound(a, 1)
                       
                z = a(i, 1)
                If Not .exists(z) Then
                    n = n + 1: .Add z, n
                     b(n, 1) = a(i, 1)
                End If
            Next
    End With
                    
With Range("b3")
     With .Resize(n, 1)
        .Value = b
          
End With
    End With
Application.ScreenUpdating = 1
End Sub

 

[Useful]

Try this

Hi ayaz ofcourse thanks for your answer but i want to know that what is wrong in my code?
(P.S i've alternative codes that removes duplicates but i want to find out my wrong step)

 

[Useful]

Hi again mr excel!
Really this is very important for me to find out where is my wrong step?
are there anybody who knows answer about my question?
I really appreciate your work!

 

[Sandeep Warrier]

Hi

The .Find method returns a range object. If you run your code, it will error out on that line.

Secondly, b has been declared Long. Its default value is 0. So if your code reaches the line cells(b,2).value, it will error out the first time, because it is looking at cells(0,2) which does not exist. So you need to initialise b as 1.

This modification to your code works.

Sub Unique()
    Dim a As Long
    Dim b As Long
    lastrow = Range("A10000").End(xlUp).Row
    b = 1
    For a = 3 To lastrow
        Range("B:B").Select
        If Selection.Find(Cells(a, 1).Value) Is Nothing Then
            Cells(b, 2).Value = Cells(a, 1).Value
            b = b + 1
        End If
    Next a
End Sub

 

[Useful]

Hi

The .Find method returns a range object. If you run your code, it will error out on that line.

Secondly, b has been declared Long. Its default value is 0. So if your code reaches the line cells(b,2).value, it will error out the first time, because it is looking at cells(0,2) which does not exist. So you need to initialise b as 1.

This modification to your code works.

Sub Unique()
    Dim a As Long
    Dim b As Long
    lastrow = Range("A10000").End(xlUp).Row
    b = 1
    For a = 3 To lastrow
        Range("B:B").Select
        If Selection.Find(Cells(a, 1).Value) Is Nothing Then
            Cells(b, 2).Value = Cells(a, 1).Value
            b = b + 1
        End If
    Next a
End Sub

Yeah that is right answer! Thanks a lot!
(now i know my wrong step- now it comes me more clear)

 

[Useful]

From the last definition arises the question Why this part

"If Isempty(selection.find(cells(a,1).value)) Then" dosn't equal to

"If Selection.Find(Cells(a, 1).Value) Is Nothing Then"