This is the abbreviation for types of variables:
& means Long
% Integer
# Double
$ String
! Single
And I still give the original code of ZVI
' Function returns sorted one-dimensional array of unique values ​​of Rng range
Function NoDups(rng As Range)
Dim arr(), i&, s$, x
' Read data into the array, for convenience restrict the last line of the sheet data
arr = Intersect(rng.Parent.UsedRange, rng).Value
' create a list
On Error Resume Next
With New Collection
    For Each x In arr()
        s = Trim(x)
        If Len(s) > 0 Then
            If IsEmpty(.Item(s)) Then
                ' fast enough option to add value to the collection with sorting (from PGC01)
                For i = 1 To .Count
                    If s < .Item(i) Then Exit For
                Next
                If i > .Count Then .Add s, s Else .Add s, s, Before:=i
            End If
        End If
    Next
    ' copy from the collection to an array
    ReDim arr(1 To .Count)
    For i = 1 To .Count
        arr(i) = .Item(i)
    Next
End With
'return an array
NoDups = arr()
End Function
There is a faster way to extract unique values with sorting.