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