Why does this code remove Duplicate Values, by showing only 1, but it does NOT show Unique values fo

[Corey]

Private Sub UserForm_Activate()
'Load the List of Customer Contacts
Application.ScreenUpdating = False
Dim LastCell As Long
Dim myrow As Long
Dim NoDupes As Collection
On Error Resume Next
LastCell = Worksheets("Contact List").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Contact List")
..Select
Set NoDupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 1) <> "" Then
NoDupes.Add .Cells(myrow, 1).Value, CStr(.Cells(myrow, 1).Value)
If Err.Number = 0 Then
ListBox1.AddItem Cells(myrow, 1)
End If
End If
Next
End With
Sheets("NavigationPage").Activate
Application.ScreenUpdating = True
End Sub


I have 2 values that are duplicates, there are loaded into the Listbox but ALL other (unique) values
are NOT populating??

Corey....

 

[George Nicholson]

If Err.Number = 0 Then
ListBox1.AddItem Cells(myrow, 1)
Else
' Reset Err.number to zero
On Error Resume Next
End If

Err.Number becomes non-zero when you try to add a dupe to the collection.
You never reset it after that, so nothing ever gets added to the list box.
Any "On Error" statement will reset Err.Number and allow an accurate
evaluation on the next loop iteration.

I have to assume that the 2 entries that do get added to the list box are
simply the first 2 rows that get processed & that the 3rd row is the first
"dupe" encountered.

HTH,

 

[merjet]

When a duplicate is found Err.Number <> 0 and it stays that way.
Between the two "End If" stmts, insert "Err.Clear". That will reset
Err.Number = 0 and allow more additions.

Hth,
Merjet

 

[Corey]

Thank you.
Perfect.
I will add that info to my growing VB vocabulary of knowledge.
When a duplicate is found Err.Number <> 0 and it stays that way.
Between the two "End If" stmts, insert "Err.Clear". That will reset
Err.Number = 0 and allow more additions.

Hth,
Merjet

 

[Tom Ogilvy]

Corey,

Remember this back on 19 February:

You have to clear the error to check the next value. Adjustments made to
your code. Same reason then.


Private Sub ListBox1_Click()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
.Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem .Cells(myrow, 60)
else
err.clear
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Just trying to help you "learn" this fact