Duplicate values in a range

[nigelandrewfoster]

Please can you show me the easiest method in VBA to check if there are any duplicate values in a particular range? (eg. B2:B151)
Thanks.
Nigel Foster

 

[JLGWhiz]

Please can you show me the easiest method in VBA to check if there are any duplicate values in a particular range? (eg. B2:B151)
Thanks.
Nigel Foster

Here is one way to find and flag your duplicates. However, there are many more approaches which can be found in the FAQ on MrExcel and on the web by searching on "Find Duplicates Excel VBA".

Code:
Sub mit()
Dim sh As Worksheet, rng As Range
Set sh = Sheets(1) 'Edid sheet name
Set rng = sh.Range("B2:B151")
For Each c In rng
If WorksheetFunction.CountIf(rng, c.Value) > 1 Then
c.Interior.ColorIndex = 15
x = x + 1
End If
Next
MsgBox "There were " & x & " cells flagged as duplicates"
End Sub
Code:

 

[HalfAce]

This can also be done without vba by using conditional formatting.
In B2, conditionally format as Formula Is and use the formula:

=COUNTIF(B$2:B$151,B2)>1

and then format to suit.
Then use the format painter to copy that format to your entire range B2:B151.

This way the background color doesn't need to be (manually) changed back when there are no more duplicates.

Hope it helps.

 

[nigelandrewfoster]

Thanks guys. I ended up using a countif array formula, but will utilise your suggestions to highlight the offending cells.