Creating a list/array in excel using VBA to get a list of unique names in a column

Question

I'm trying to create a list of unique names in a column but I've never understood how to use ReDim correctly, could someone help finish this off for me and explain how it's done or better suggest an alternative better/faster way.

Sub test()
    LastRow = Range("C65536").End(xlUp).Row
    For Each Cell In Range("C4:C" & LastRow)
        OldVar = NewVar
        NewVar = Cell
        If OldVar <> NewVar Then
            `x =...
        End If
    Next Cell
End Sub

My Data is in the format of:

Stack
Stack
Stack
Stack
Stack
Overflow
Overflow
Overflow
Overflow
Overflow
Overflow
Overflow
Overflow
.com
.com
.com

So essentially once it has the name once it will never popup again later on down in the list.

At the end the array should consist of:

    Stack
    Overflow
    .com

Answer 1

You can try my suggestion for a work around in Doug's approach.
But if you want to stick with your logic though, you can try this:

Option Explicit

Sub GetUnique()

Dim rng As Range
Dim myarray, myunique
Dim i As Integer

ReDim myunique(1)

With ThisWorkbook.Sheets("Sheet1")
    Set rng = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
    myarray = Application.Transpose(rng)
    For i = LBound(myarray) To UBound(myarray)
        If IsError(Application.Match(myarray(i), myunique, 0)) Then
            myunique(UBound(myunique)) = myarray(i)
            ReDim Preserve myunique(UBound(myunique) + 1)
        End If
    Next
End With

For i = LBound(myunique) To UBound(myunique)
    Debug.Print myunique(i)
Next

End Sub

This uses array instead of range.
It also uses Match function instead of a nested For Loop.
I didn't have the time to check the time difference though.
So I leave the testing to you.