List box data without duplicates

redeye

New Member
Joined
Jun 12, 2002
Messages
9
I have a column of data that I would like to add to a list box. How do I add to contents of the column less any duplicates?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
On 2002-09-05 22:26, redeye wrote:
I have a column of data that I would like to add to a list box. How do I add to contents of the column less any duplicates?


One way to do this is to Basically
1) Clear a Hidden sheet say Column A ready
for a sorted list.
2) Use AdvancedFilter to copy the list to Column A of the hidden sheet and remove all dupes.
3) Parse the address of the unique items
4) Set a reference to this new list eg
'// Clear old ListBox RowSource
.RowSource = vbNullString
'// Parse new one
.RowSource = strRowSource





_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails
image.gif

This message was edited by Ivan F Moala on 2002-09-06 00:49
 

GeorgeB

Board Regular
Joined
Feb 16, 2002
Messages
239
This will do it.

Private Sub UserForm_Initialize()
Dim List As New Collection
Dim rnArea As Range, rnCell As Range
Dim vaValues As Variant

Set rnArea = ActiveSheet.Range(Range("C1"), Range("C65536").End(xlUp))

On Error Resume Next
For Each rnCell In rnArea
List.Add rnCell.Value, CStr(rnCell.Value)
Next rnCell
On Error GoTo 0

For Each vaValues In List
UserForm1.ListBox1.AddItem vaValues
Next

End Sub
 

Forum statistics

Threads
1,144,218
Messages
5,723,069
Members
422,477
Latest member
pete101

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