Use VBA to get Unique values for use within VBA?

Question

I currently would use something like this either with Range, Cells or the like many different ways same basic principle.

Range("A1", Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range("IV1"), Unique:=True

Dim myArr as Variant 
myArr = Range("IV1", Range("IV1").End(xlDown))
Columns("IV").Delete

Is there a way to directly load those unique values into any type of object in VBA without the need to copy to another location?

Similar: http://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array IMO the best way is to use a dictionary. — mechanical_meat, Apr 03 '13 at 22:16
Has nothing to do with that. That is about working with VBA built arrays mine is getting a Unique list out of a Column and into an Array. — user2140261, Apr 03 '13 at 22:17
@bernie: Sorry just saw your comment. Have deleted my answer :) — Siddharth Rout, Apr 03 '13 at 22:19
@SiddharthRout: there is no need to delete! Please undelete you answer. I'm sure it is a very good answer. — mechanical_meat, Apr 03 '13 at 22:19
@bernie: it's almost same as your answer except it picks the values from the range rather than an array :) — Siddharth Rout, Apr 03 '13 at 22:21
Oh, that wasn't my answer... But, seriously, please do not delete your answer. — mechanical_meat, Apr 03 '13 at 22:21
I was hoping for something entirely different then that question. I could possibly use the info in that post to read in All data column then use the code from that to find unique buts, I was hoping there was a more direct way. I can only think of something like "for each value in range if value not in array add other wise skip" I was looking for something built in like myArr = Range("A1:A22").Unique = True. — user2140261, Apr 03 '13 at 22:22
@bernie: You are very kind. Though I still insists that both answers are very identical. :) — Siddharth Rout, Apr 03 '13 at 22:33

Siddharth Rout · Accepted Answer · 2013-04-03T22:31:35.550

6

You can use a Collection Object to create unique entries. For example

Sub Sample()
    Dim Col As New Collection
    Dim itm
    Dim i As Long
    Dim CellVal As Variant

    '~~> Lets say looping through Row 1 to 22 For 
    '~~> Range("A1:A22") as mentioned in your recent comment
    For i = 1 To 22
        CellVal = Sheets("Sheet1").Range("A" & i).Value
        On Error Resume Next
        Col.Add CellVal, Chr(34) & CellVal & Chr(34)
        On Error GoTo 0
    Next i

    For Each itm In Col
        Debug.Print itm
    Next
End Sub

ScreenShot:

enter image description here

edited Apr 03 '13 at 22:31

answered Apr 03 '13 at 22:18

Siddharth Rout

147,039
17
206
250

So, a collection will throw an error if the same value is added twice? – user2140261 Apr 03 '13 at 22:24
No it will not since we are using OERN – Siddharth Rout Apr 03 '13 at 22:24
OERN = `On Error Resume Next` – Siddharth Rout Apr 03 '13 at 22:25
2

But the collection is still throwing an error you are simply handling it. – user2140261 Apr 03 '13 at 22:25
See my previous comment :) – Siddharth Rout Apr 03 '13 at 22:26
4

Ok, Well I accepted this as the answer but I much rather an explanation or a point in the right direction then a piece of code with no description on how or why it works. And when I asked about the error handling and why it works I simply got a response that it does work. This is not what this site is for, it is not to ask for an answer and get it but to learn. So for anyone in the future. The Collection variable only works on 2 conditions. One you associate a key with the values you are adding, and 2 ignore the errors that tell you when you are duplicating keys. – user2140261 Apr 04 '13 at 00:01
1

@user2140261: I just re-read the comments now. I see the comment was edited to `But the collection is still throwing an error you are simply handling it.` Had I seen the new comment then I would have definitely explained it. What you said in your last comment about about learning is very true :) Apologies for missing that comment. – Siddharth Rout Apr 04 '13 at 05:39
A collection item cannot have the same Key as another item. The values can be the same, but the Keys (second argument to Add method) must be unique. By using the value as the Key (I use `Cstr(CellVal)` b/c the Key must be a String data type), you are ensuring that only unique values are in the collection. You are correct that an error is thrown. The Add method fails due to the error, but the code happily continues trying to add more stuff to the collection bc the error is being ignored. – Dick Kusleika Apr 04 '13 at 13:01
this line return error `For Each itm In Col` *Type mismatch (Error 13)* – Rocketq Jul 10 '15 at 19:18

Use VBA to get Unique values for use within VBA?

1 Answers1

Linked