create list of unique values

[blackbox]

I know the Advanced filter does this but I couldn't get it to work in vba, so I tried the following and couldn't get it to work either
In column A I have values starting at row 4, ranging up to row 1004.

in coulumn Q (starting at row 3) I'd like a list of unique entries from column A

I know this line is my problem

If Range("A" & I) <> Range("Q4:Q30") Then

but i'm not sure how to compare against all values in a range

Sub FilterSymbol()
Dim I As Integer
Dim X As Integer
X = Range("O2")
I = 4

Do
If Range("A" & I) <> Range("Q4:Q30") Then
Range("Q" & I) = Range("A" & I)
I = I + 1
Else: I = I + 1
End If
Loop Until I >= X
 
End Sub

 

[blackbox]

here's a few, there's about 90 different symbols in total but most are rarely used, generaly only see 20 or so

/ESU7
/ER2U7
/NQU7

 

[jindon]

Hummm
Don't undeerstand why you get such error... I've never had.
Can you try the code? (It doesn't sort the list though)

Sub test()
Dim a, e, x
a = Range("a4:a1003").Value
With CreateObject("Scripting.Dictionary")
     .CompareMode = vbTextCompare
     For Each e In a
          If Not .exists(e) Then .add e, Nothing
     Next
     UserForm1.ListBox1.List = .keys
End With
End Sub

 

[blackbox]

i get Run-time error 424: Object required on

UserForm1.ListBox1.List = .keys

do I need to set a reference or something?

 

[jindon]

That seems you don't have either UserForm1 or ListBox1
Can you paste the code onto the form module?

Private Sub UserForm_Initialize()
Dim a, e, x
a = Range("a4:a1003").Value
With CreateObject("Scripting.Dictionary")
     .CompareMode = vbTextCompare
     For Each e In a
          If Not .exists(e) Then .add e, Nothing
     Next
     Me.ListBox1.List = .keys
End With
End Sub

 

[blackbox]

ok, inserted a userform and added code.
tried to add a list box but got "Could not complete the operation due to error 800a0011"

is this goin to show the list in the userform or in the worksheet?

 

[jindon]

Your original code posted for yourself is populating the list in the ListBox1 on UserForm1.

What are you trying to do?

Are you using Userform? or not?

 

[blackbox]

I wanted a list of unique symbols created in the worksheet, column Q starting at row 3

the code I was tryin earlier did not create a list, only showed the first value from column A

 

[jindon]

No wonder
try
Note
Assuming you have ListBox1 on UserForm1

Sub test()
Dim a, e, x
a = Sheets("Sheet1").Range("q4:q1003").Value
With CreateObject("Scripting.Dictionary")
     .CompareMode = vbTextCompare
     For Each e In a
          If Not .exists(e) Then .add e, Nothing
     Next
     x = .keys
End With
SortA x, 0, UBound(x)
UserForm1.ListBox1.List = x
End Sub

Private Sub SortA(ary, LB, UB)
Dim i As Long, ii As Long, M As Variant, temp As Variant
i = UB : ii = LB
M = ary(Int((LB + UB)/2))
Do While ii <= i
     Do While ary(ii) < M
          ii = ii + 1
     Loop
     Do While ary(i) > M
          i = i - 1
     Loop
     If ii <= i Then
          temp = ary(ii) : ary(ii) = ary(i) : ary(i) = temp
          i = i - 1 : ii = ii + 1
     End If
Loop
If LB < i Then SortA ary, LB, i
If ii < UB Then SortA ary, ii, UB
End Sub

 

[blackbox]

removed

UserForm1.ListBox1.List = x

nothing happens when I run it

then I noticed

a = Sheets("Sheet1").Range("q4:q1003").Value

changed range to A4:A1003 (my data renge)
and get Runtime error 13: Type mismatch on

Do While ary(i) > M

I don't see naything in the code that creates the list of unique values in column Q

 

[jindon]

removed

UserForm1.ListBox1.List = x

nothing happens when I run it

If you remove that line nothing ever happen.
That is the like to populate the list.

then I noticed

a = Sheets("Sheet1").Range("q4:q1003").Value

changed range to A4:A1003 (my data renge)

Didn't you say your data start row4 of colQ in your previous post?

and get Runtime error 13: Type mismatch on

Do While ary(i) > M

I don't see naything in the code that creates the list of unique values in column Q

Sorry I don't seem to understand what you are trying to do.