Excel VBA Get List of Unique Values in Column without Dynamic Range [duplicate]

First, create a dictionary object to store unique values, then loop through Column C of DataSheet (starting at row 2 to skip header), then add each non-empty value to the dictionary (dictionaries automatically handle uniqueness), finally transfer the unique values from the dictionary to Column 16 of the Divide sheet

Here is an example of dictionary usage: dict(value) = 1 adds unique values automatically, dict.Keys contains all unique value, and no need to check for duplicates

Sub ExtractUniqueValues()
    Dim dict As Object
    Dim cell As Range
    Dim i As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In Sheets("DataSheet").Range("C2:C" & Sheets("DataSheet").Cells(Rows.Count, "C").End(xlUp).Row)
        If Not IsEmpty(cell) Then dict(cell.Value) = 1
    Next
    
    ' Output
    i = 2
    For Each key In dict.Keys
        Sheets("Divide").Cells(i, 16) = key
        i = i + 1
    Next
End Sub

you could use Excel built in RemoveDuplicates function

Sub GetUniqueValues()
    
    Dim dataRng As Range
        Set dataRng = Sheets("DataSheet").Range("C2", Sheets("DataSheet").Cells(Sheets("DataSheet").Rows.Count, "C").End(xlUp)) ' store data range
        With Sheets("Divide").Cells(2, 16).Resize(dataRng.Rows.Count)  ' reference output sheet range
            .Value = dataRng.Value ' paste data values 
            .RemoveDuplicates Columns:=1, Header:=xlNo ' remove duplicates
        End With
    
End Sub