ComboBox Duplicate error

  • Hi all, can someone be kind enough to help me out here please? I have the following code below where the combobox lists dates from a column from my users sheet. All works well (apart from the dates are over the place and not in order) but I just can't seem to stop the duplicate dates showing in the drop down (I can't change the user sheet as data in there is required), does anyone know how to stop the duplicates and put it in date order? Thanks in advance.


    [VBA]Private Sub UserForm_Initialize()
    SortStartDateRange

    Dim ws As Worksheet
    Dim v
    Dim n As Long
    Set ws = Worksheets("Users")
    v = ws.Range("K2:K500").value
    For n = LBound(v) To UBound(v)
    v(n, 1) = Format(v(n, 1), "dd/mm/yyyy")
    Next
    Me.OutListCombo.List = v

    SortStartDateRange
    End Sub[/VBA]

  • I have this code to grab cell values and add them to a collection and then to name the items by key = their value as a string. This forces an error anytime a duplicate value is encountered since a key must be unique. I basically ignore the error using the [VBA]on error resume next[/VBA]. To sort by date can you not sort the list and then perform an operation such as this?


    [VBA]
    dim Cell as object
    dim Ws_1 as worksheet
    Dim cUnique as New Collection
    Dim vNum As Variant
    dim LastCell as long


    On Error Resume Next
    For Each cell In Ws_1.Range(Ws_1.Range("C2"), Ws_1.Range("C" & LastCell)).SpecialCells(xlCellTypeVisible)
    cUnique.Add cell.Value, CStr(cell.Value)
    Next cell


    For Each vNum In cUnique
    Me.ComboBox4.AddItem vNum
    Next vNum


    On Error GoTo 0
    [/VBA]