Re: Sorting dates in a combobox

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 07/01/04 

Date: Thu, 1 Jul 2004 12:32:04 +0100

Mark,

Here is some code thata ssumes that dates are in column 1, the combobox is a
Forms combo called Drop Down 1

'-----------------------------------------------------------------
Sub LoadUniqueToCombo()
'-----------------------------------------------------------------
Dim cRows As Long

    cRows = Cells(Rows.Count, "A").End(xlUp).Row

    Range("A1").Resize(cRows).Sort , KEY1:=Range("A1")

    Range("A1").EntireColumn.Insert
    Range("A1").Formula = "=COUNTIF($B$1:B1,B1)>1"
    Range("A1").AutoFill Destination:=Range("A1").Resize(cRows)

    Range("A1").EntireRow.Insert
    Columns("A:A").AutoFilter Field:=1, Criteria1:="TRUE", Operator:=xlAnd

    With Range("B1:B" & cRows + 1)
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    Columns("A:A").EntireColumn.Delete

    cRows = Cells(Rows.Count, "A").End(xlUp).Row
    With Active***.DropDowns("Drop Down 1")
        .ListFillRange = "$A$1:$A$" & cRows
        .DropDownLines = 8
        .ListIndex = 1
    End With

End Sub 

-- 
HTH
Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Mark Rosenkrantz" <contact@rosenkrantz.nl> wrote in message
news:cc0iv6$sao$1@reader08.wxs.nl...
> Bob and other;
>
> The sourcelist is on a work***.
>
> Mark.
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:ehAxnJ0XEHA.2716@tk2msftngp13.phx.gbl...
> > Mark,
> >
> > You need to filter the data and sort it before it hits the combo. Where
is
> > it a sourced from, a work***, a database?
> >
> > -- 
> >
> > HTH
> >
> > Bob Phillips
> >     ... looking out across Poole Harbour to the Purbecks
> > (remove nothere from the email address if mailing direct)
> >
> > "Mark Rosenkrantz" <contact@rosenkrantz.nl> wrote in message
> > news:cc0g8l$eqh$1@reader13.wxs.nl...
> > > Dear all;
> > >
> > > I have a list of dates in the format dd/mm/yyyy
> > > These dates are not unique.
> > > The list ( 2000 + items ) looks like :
> > >
> > > 29/06/2004
> > > 30/06/2004
> > > 29/06/2004
> > > 01/07/2004
> > > 30/06/2004
> > > 29/06/2004
> > > 01/07/2004
> > >
> > > I want that list displayed in a combobox so I can choose from that
list,
> > but
> > > I only want to see the unique items.
> > > The list must be sorted in chronological order.
> > > It thus should look like :
> > >
> > > 29/06/2004
> > > 30/06/2004
> > > 01/07/2004
> > >
> > > How do I do that with VBA ?
> > >
> > > Mark.
> > >
> > >
> >
> >
>
>