VBA Dynamic way to copy and paste unique values

I'm not for sure I understood your request.
If I did, then:
Backup your data.
Copy the code below to a standard module (if you don't know how, ask).
Run: Run_GetUniqueValues

It was unclear to me whether you wanted a list of all unique values from both columns C & D (or just C).

Thus in the code below you will see both:

    Set rg = Range("C2:C" & nLastRow)
    ' Set rg = Range("C2:D" & nLastRow)

For both C & D, remove the apostrophe and add it to the line above.

The function: GetUniqueValues collects unique values from any range and stores it in a string separated by the vbCR character. Then Run_GetUniqueValues puts these values in a string array.

You didn't mention if you wanted these values sorted. I added a Bubble sort function to sort these values.

Then leaving an empty row, I copied these values below the previously last row of data in column C.

Function GetUniqueValues(rg As Range) As String
    Dim vArray As Variant, i As Long, j As Long, sList As String
    vArray = rg.Value
    sList = vbCr
    For i = LBound(vArray, 1) To UBound(vArray, 1)
        For j = LBound(vArray, 2) To UBound(vArray, 2)
            If InStr(1, sList, vbCr & vArray(i, j) & vbCr) = 0 Then
                sList = sList & vArray(i, j) & vbCr
            End If
        Next j
    Next i
    If Len(sList) > 2 Then
        GetUniqueValues = Mid$(sList, 2, Len(sList) - 2)
    End If
End Function

Function BubbleSortsArray(ByRef sArray() As String)
    Dim i As Long, j As Long, s As String
    
    For i = LBound(sArray) To UBound(sArray) - 1
        For j = i + 1 To UBound(sArray)
            If sArray(i) > sArray(j) Then
                s = sArray(j)
                sArray(j) = sArray(i)
                sArray(i) = s
            End If
        Next j
    Next i
End Function

Sub Run_GetUniqueValues()
    Dim rg As Range, sArray() As String, nLastRow As Long, i As Long
    
    nLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    Set rg = Range("C2:C" & nLastRow)
    ' Set rg = Range("C2:D" & nLastRow)
    sArray = Split(GetUniqueValues(rg), vbCr)
    BubbleSortsArray sArray
    For i = LBound(sArray) To UBound(sArray)
        Cells(nLastRow + 2 + i, "C") = sArray(i)
    Next i
End Sub

Dear StevenM
Is is possible to sort the values based on alphanumerically by using your Function BubbleSortsArray(ByRef sArray() As String) fucntion.


Thanks and regards,

Sudhakar

Your