check duplicated items

Question

Is there any way to check duplicated items in one go instead of doing it separately as shown in the code below?

Private Sub checkDuplicates(wks As Worksheet)
    Dim lastRow As Long: lastRow = Cells(wks.Rows.Count, "E").End(xlUp).Offset(1, 0).row
    Dim n, i, j As Long, found As Range

    For n = 0 To ListboxResult.ListCount - 1
        Set found = wks.Range("E4", "E" & lastRow).Find(Me.ListboxResult.List(n, 1))
        If Not found Is Nothing Then
            MsgBox "Item " & Me.ListboxResult.List(n, 1) & " is duplicated", vbOKOnly, "Duplicated items"
        Else
            Call addToNewRow(wks, lastRow, n)
            
            lastRow = lastRow + 1
        End If
    Next n
End Sub

Answer 1

I don't think that there is a way to do this without a loop. All you have to do is to avoid that the duplicate-message is shown to the user within the loop.

Instead, collect all duplicates and when the loop stops (and you found any duplicates), show it to the user.

Your code could look like this:

Dim duplicates As String, duplicateCount As Long
For n = 0 To ListboxResult.ListCount - 1
    Dim item As String
    item = Me.ListboxResult.List(n, 1)
    Set found = wks.Range("E4", "E" & lastRow).Find(item)
    If Not found Is Nothing Then
         duplicates = duplicates & IIf(duplicateCount = 0, "", ", ") & item
         duplicateCount = duplicateCount + 1
    Else
        Call addToNewRow(wks, lastRow, n)
        lastRow = lastRow + 1
    End If
Next n

If duplicateCount = 1 Then
    MsgBox "Item " & duplicates & " is duplicated", vbOKOnly, "Duplicate item"
ElseIf duplicateCount > 1 Then
    MsgBox "Items " & duplicates & " are duplicated", vbOKOnly, duplicateCount & "Duplicate items"
End If