C
Corey
The following code is suppose to remove the duplicate values, but it is not.
Listbox1.value when selected populates the listbox2
Is use the same code to initialise the form and populate Listbox1 which DOES remove any duplicates.
But, the below code does not.
Am i missing something obvious ?
Private Sub ListBox1_Change()
Application.ScreenUpdating = False
If ListBox2.ListCount > 0 Then ListBox2.Clear
Dim LastCell As Long
Dim myrow As Long
Dim NoDupes As Collection
On Error Resume Next
LastCell = Worksheets("Data2").Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data2")
.Select
Set NoDupes = New Collection
For myrow = 1 To LastCell
If ListBox1.Value = .Cells(myrow, 2).Value And .Cells(myrow, 2) <> "" Then
NoDupes.Add .Cells(myrow, 3).Value, CStr(.Cells(myrow, 3).Value)
If Err.Number = 0 Then
If .Cells(myrow, 3) <> "" And .Cells(myrow, 2) = ListBox1.Value Then
ListBox2.AddItem .Cells(myrow, 3) ' <=== DUPLICATES are still DISPLAYED
End If
Else
Err.Clear
End If
End If
Next
End With
Sheets("POST Project Report").Select
Application.ScreenUpdating = True
End Sub
Listbox1.value when selected populates the listbox2
Is use the same code to initialise the form and populate Listbox1 which DOES remove any duplicates.
But, the below code does not.
Am i missing something obvious ?
Private Sub ListBox1_Change()
Application.ScreenUpdating = False
If ListBox2.ListCount > 0 Then ListBox2.Clear
Dim LastCell As Long
Dim myrow As Long
Dim NoDupes As Collection
On Error Resume Next
LastCell = Worksheets("Data2").Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data2")
.Select
Set NoDupes = New Collection
For myrow = 1 To LastCell
If ListBox1.Value = .Cells(myrow, 2).Value And .Cells(myrow, 2) <> "" Then
NoDupes.Add .Cells(myrow, 3).Value, CStr(.Cells(myrow, 3).Value)
If Err.Number = 0 Then
If .Cells(myrow, 3) <> "" And .Cells(myrow, 2) = ListBox1.Value Then
ListBox2.AddItem .Cells(myrow, 3) ' <=== DUPLICATES are still DISPLAYED
End If
Else
Err.Clear
End If
End If
Next
End With
Sheets("POST Project Report").Select
Application.ScreenUpdating = True
End Sub