NoDupes with ComboBox Fill > Date Problem?

I_AM

Board Regular
Joined
Jul 2, 2002
Messages
139
Hi,
In Column A I have Dates entered thus-01/01/04,02/01/04 etc.. but formatted to show-Jan-04. The following Code will Show 2 entries of Jan-04 in ComboBox1.

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim ListSort As Range

With Sheet1
.Activate
Range(("A2"), Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).Activate
Selection.Activate
On Error Resume Next
For Each Cell In Selection
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
For Each Item In NoDupes
Main_Menu.ComboBox1.AddItem Format(Item, "[$-409]mmm-yy;@")
Next Item

How to get the Code to display only one entry?

Thanks Regards Ron
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

I_AM

Board Regular
Joined
Jul 2, 2002
Messages
139
Yes So did I! :oops: Just couldnt rid myself of the multiple entries. (Not through lack of trying though) Will console myself by at least playing around with the correct line! Works a treat Thanks (y)
Regards Ron
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
Try:

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim ListSort As Range
Dim FoundDupe As Boolean
Dim n As Long
Dim CellDate As Variant

For Each Cell In Sheet1.Range(("A2"), Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
CellDate = Month(Cell) & "/" & Year(Cell)
FoundDupe = False
If Cell.Address <> "$A$2" Then
For n = 1 To NoDupes.Count
If CellDate = NoDupes.Item(n) Then FoundDupe = True
Next n
End If
If FoundDupe = False Then NoDupes.Add CellDate
Next Cell

For Each Item In NoDupes
Main_Menu.ComboBox1.AddItem Format(Item, "[$-409]mmm-yy;@")
Next Item


Or, if you don't need to use your NoDupes collection somewhere else, you can get rid of it altogether, using:

Dim AllCells As Range, Cell As Range
Dim ListSort As Range
Dim FoundDupe As Boolean
Dim n As Long
Dim CellDate As Variant

For Each Cell In Sheet1.Range(("A2"), Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
CellDate = Format(Month(Cell) & "/" & Year(Cell), "[$-409]mmm-yy;@")
FoundDupe = False
If Cell.Address <> "$A$2" Then
For n = 0 To Main_Menu.ComboBox1.ListCount - 1
If CellDate = Main_Menu.ComboBox1.List(n) Then FoundDupe = True
Next n
End If
If FoundDupe = False Then Main_Menu.ComboBox1.AddItem CellDate 'Format(CellDate, "[$-409]mmm-yy;@")
Next Cell

PS There are a few extra declarations that are not necessary, but I'm assuming you are using the variables somewhere else.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top