Sorting dates in a combobox

  • Thread starter Mark Rosenkrantz
  • Start date
M

Mark Rosenkrantz

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.
 
B

Bob Phillips

Mark,

You need to filter the data and sort it before it hits the combo. Where is
it a sourced from, a worksheet, a database?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

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 ActiveSheet.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)
 
M

Mark Rosenkrantz

Bob;

Thanks.
I'll try that one.

Mark.

Bob Phillips said:
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 ActiveSheet.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)

Where
Click to expand...
 
Top