cwb1021
03-25-2017, 09:35 AM
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.
Private 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
18766
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
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.
Private 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
18766
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