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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,576
Messages
6,143,314
Members
450,477
Latest member
teresab543

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
Back
Top