I need to identify duplicate names and addresses in an Excel file.

I have the following syntax, which will highlight the specified text:


Dim strClient As String
strClient = Application.InputBox("Please enter a client name.", "Search for client name")
    
With Worksheets(1).Range("c1:c500")
    Set c = .Find(strClient, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.Pattern = xlPatternGray50
            c.Interior.PatternColorIndex = 28
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub

Can anyone tell me how to count the number of records with the specified text, or identify duplicates, so I need not scroll through the entire file?  This database will likely be populated with 5,000 records.

Many thanks.

Try this code then let me know if you have any problems.

Private Sub FindMatches()
Dim AryPositions() As String
Dim strClient As String
i = 1
strClient = Application.InputBox("Please enter a client name.", "Search for client name")
    
With Worksheets(1).Range("c1:c500")
    Set c = .Find(strClient, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.Pattern = xlPatternGray50
            c.Interior.PatternColorIndex = 28
            ReDim Preserve AryPositions(i)
            AryPositions(i) = c.Address
            i = i + 1
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

MsgBox "There were " & UBound(AryPositions()) & " matches found."
answer = MsgBox("Would you like to View Cells?", vbYesNo)
If answer = 6 Then
    For i = 1 To UBound(AryPositions())
        Worksheets(1).Range(AryPositions(i)).Select
        MsgBox "Continue?", vbOKOnly
    Next i
End If
End Sub

Thank you!  It works great.  

I am not familiar with the "ReDim Preserve...."  syntax or "UBound".  I will have to so some research so I fully benefit from your efforts.  

I am most appreciative.