Find duplicate rows in Excel

[soumya_bhatta]

How do I find(not delete) duplicate rows in an excel spreadsheet(about 3000 rows), bring them adjucent and may be highlight them?

Before posting, I searched this forum with "duplicate rows".

[WorkHorse]

The AdvancedFilter will find all "unique" items. So, everything else must be a duplicate. If you highlight everything, then unhighlight the unique items, you have highlighted all duplicates.

Sub MakeDupsYellow()
    
    Dim myRange         As Range
    Dim strColToSort    As String
 
    'Note: first cell in column is considered a heading
    '      and is not included in the filter.
 
    strColToSort = "A"
 
    Application.ScreenUpdating = False
    
    Set myRange = Columns(strColToSort & ":" & strColToSort)
    myRange.Interior.ColorIndex = 36
    myRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    myRange.Interior.ColorIndex = xlNone
    ActiveSheet.ShowAllData
    
    Application.ScreenUpdating = True
 
End Sub

[soumya_bhatta]

Thanks WorkHorse. Works super!

[RedLady2]

How do I find the duplicate rows (not delete) when both the first and second column together (first name and last name) need to be found.

[westconn1]

you should have started a new thread for your question rather than resurrecting one from nearly 10 years ago

you can use countif on first column, if > than1 then loop through with vlookup and check matches in second column, i believe, there would even be some formula to do this