Use VBA to get Unique values for use within VBA?
27,040
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:
Comments
-
user2140261 about 4 years
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?