Populate ComboBox with unique and Visible from column B

Excel 2010, VBA Userform with ComboBox

I am able to populate ComboBox2 with the Filtered and visible data but, it also includes duplicates. How can I populate ComboBox2 with no duplicate data and only with the Visible data after its filtered.

Below is code that I am using:

'The Data has already been filtered and the combo displays the Filtered and visible data. I just need to remove the duplicates

Set rng = .Range("AllData").Columns(2).SpecialCells(xlCellTypeVisible)
For Each cel In rng

With Me.ComboBox2
.AddItem cel.Offset(0, 0).Value

End With


Any help is appreciated.
Regards

Hi davesexcel-

Thank you for responding and the additional information.

I have attached the excel file and a word doc with screenshots. Please let me know if additional info is needed.

Regards,

To get unique items in combobox take a look at Dictionarys.
http://www.snb-vba.eu/VBA_Dictionary_en.html

Hi bakerman2-

I will take a look at Dictionary's.

Thanks and regards,

bakerman2 is correct, and provides an excellent tutorial link. Using a 'Scripting Dictionary' is an excellent way to implement what you want. A 'Scripting Dictionary' is a very good way to identify the unique items in a list.

Unfortunately, the link is an excellent reference, but not very good if you have no Scripting Dictionary experience.

You should be able to integrate the following code (tested with your file and working) into your routine that populates the ComboBox.

Please Login or Register  to view this content.

Lewis

Hi LJMetzger-

Thank you!

I added the code for each Combo box and they are working. The only issue I have is that the Expiration date is showing up as numbers and not in the date format.

Please see attachment.

Regards,

Hi,

Excel displays dates in formats that we select that is usually easy for us to read, such as '08/08/2015". Internally Excel stores the dates as numbers. The VBA code extracted the date as a number, and we had to use the format statement to put the date back into prettyprint.

UserForms have no idea what a number is. Everything in a UserForm is stored as text.


Try the macro now. Changes in red:

Please Login or Register  to view this content.

Lewis

The modification has fixed it.

Thank you again LJMetzger for your time.

Thanks to all.

Resolved.

Regards,

I'm glad you solved your problem. I was only too happy to help.