Use VBA to get unique values ​​to use in VBA?

Question

Currently, I would use something like this with Range, Cells or the like, in different ways, in the same basic way.

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 these unique values ​​into any type of object in VBA without having to copy to another location?

Answer 1

You can use Collection Object to create unique records. for instance

 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 

Screen shot