Experts,
I have a user form which takes data from a worksheet, populates 4 listboxes and then filters the data based on selections. The problem I've run in to is showing only unique values in the listboxes, as the data in the each column is repeated many times.
The following code uses with and for statements repeated for each list box, which first sorts the data and then removes duplicate values. It works to an extent, but for some of the listboxes does not show all of the values in the range, and for the range with numbers, repeats blocks of sorting data.
So, I have 3 questions:
1. How can I get listbox 2 to show only unique values in ascending order (and exclude the column header)?
2. How can I get listbox 3 to show all the unique values in the range?
3. Could this procedure be tightened up to include all 4 list boxes without repeating the same process for each?
I've also attached the workbook which has all the code for the userform.
userform1pic.jpgPrivate Sub UserForm_Activate() Dim i As Integer, j As Integer, z As Integer Dim x As Long, y As Long Dim Temp As Variant Dim nonempty As Long Me.ListBox1.List = Range("A1:A1000").Value With ListBox1 For i = 0 To .ListCount - 2 For j = i + 1 To .ListCount - 1 If .List(i) > .List(j) Then Temp = .List(j) .List(i) = Temp End If Next j Next i End With For i = ListBox1.ListCount - 1 To 1 Step -1 If ListBox1.List(i) = ListBox1.List(i - 1) Then ListBox1.RemoveItem i End If Next I Me.ListBox2.List = Range("B1:B1000").Value 'Code for listbox2 ??? For i = ListBox2.ListCount - 1 To 1 Step -1 If ListBox2.List(i) = ListBox2.List(i - 1) Then ListBox2.RemoveItem i End If Next i Me.ListBox3.List = Range("C1:C1000").Value With ListBox3 For i = 0 To .ListCount - 2 For j = i + 1 To .ListCount - 1 If .List(i) > .List(j) Then Temp = .List(j) .List(i) = Temp End If Next j Next i End With For i = ListBox3.ListCount - 1 To 1 Step -1 If ListBox3.List(i) = ListBox3.List(i - 1) Then ListBox3.RemoveItem i End If Next i Me.ListBox4.List = Range("D1:D1000").Value With ListBox4 For i = 0 To .ListCount - 2 For j = i + 1 To .ListCount - 1 If .List(i) > .List(j) Then Temp = .List(j) .List(i) = Temp End If Next j Next i End With For i = ListBox4.ListCount - 1 To 1 Step -1 If ListBox4.List(i) = ListBox4.List(i - 1) Then ListBox4.RemoveItem i End If Next i End Sub
Thanks to these guys here for the base code: http://www.ozgrid.com/forum/showthread.php?t=173486
Any help is greatly appreciated!
Thanks,
Chris