Thread: VBA Macro checks for duplicates,works great, but wish to add a select row

Hi,

I've found this vba script that locates duplicate values in a excelsheet.
If is has found some duplicate values, it gives these cells a certain color.
If it finds a single rows without a duplicate, it deletes this row.
It works like a charm, but i would like to have a modification on this script.

It would be great to include a option, so that it only checks 1 column (B) for duplicate values.
I tried to include this line:

Columns("B:B").Select

But that does not work. Is there anyone who can give me a solution, or at least a kick in the right direction ?

Script:
================================================================================

Public Sub HighlightDuplicateRows()
'
' This macro highlights duplicate rows in the selection and deletes
' unique rows. Duplicates are counted in the COLUMN of the active cell.

Dim r As Long
Dim C As Range
Dim V As Variant
Dim Rng As Range
Dim Color As Integer

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
Color = 44
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If V <> V1 Then
Color = Color - 2
If Color = 34 Then Color = 44
End If
V1 = V
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Select
With Selection.Interior
.ColorIndex = Color
.Pattern = xlSolid
End With
Else
Rng.Rows(r).EntireRow.Delete
End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

=====================================================================================

Thanks in advance (a lot !!)
Mesjoggah

@arlu1201
The line

If columns(2).Rows.Count > 1 Then

will give 65536 in Excel 2003, 1 million+ in Excel 2007 etc. What does this line check then?

Thanks a lot !
Works perfect !
Thank