This takes something like a list of names and returns a dictionary of unique ones you can loop through

VBA - Generate Unique Dictionary of Strings from Range

 uniqueValues(Range("sheet!D:D"))
 
 Function uniqueValues(InputRange As Range)
    Dim dict As New Scripting.Dictionary
    Dim cell As Range
    For Each cell In InputRange
     If cell.Rows.Hidden = False Then
            'if this row gives you a error its because one of your cells has a #NA, fix the data!
            If cell.Value <> "" Then
                If Not dict.Exists(cell.Value) Then
                dict.Add cell.Value, cell.Value
            End If
        End If
     End If
    Next cell
    Set uniqueValues = dict
End Function