Marco Help

[LaDdIe]

Hiya, (Sorry forgot to include example code).

I've been using this macro to check for duplicate entries in a colomn,

'Check for duplicate names
Sub HighlightDuplicates()
Dim Cell As Range
Dim Cell_Range As Range
Dim MyCollection As New Collection

n = 0
'Find last cell entry in the row
LastEntry = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Address
'Define the range to examine
Set Cell_Range =
ActiveSheet.Range("A5,A7,A9,A11,A13,A15,A17,A19,A21,A23,A25,A27,A29,A31,A33,A35,A37,A39,A41,A43,A45")

For Each Cell In Cell_Range
On Error Resume Next
MyCollection.Add Item:="1", Key:=Cell.Text
If Err.Number = 457 Then
Cell.Interior.ColorIndex = 36
Err.Clear
n = n + 1
End If
Next Cell

If n > 0 _
Then
If n = 1 _
Then
v = "is "
noun = " duplicate, Please Check Names."
Else:
v = "are "
noun = " duplicates, Please Check Names"
End If

MsgBox ("There " & v & n & noun)
Else:
MsgBox ("There are no duplicates.")
End If

End Sub


However, some cell are blank at the moment and the macro is counting them as
duplicate entries, is there a way that the macro can be modified so that
blank cells are ignored from the count.

Thanks to anyone who can help.

Respectx
Laddie.

 

[Vergel Adriano]

Hi,

here's one way:

If Cell.Text <> "" Then
MyCollection.Add Item:="1", Key:=Cell.Text
End If

 

[LaDdIe]

Spot on,

Thanks Vergel,

Hi,

here's one way:

If Cell.Text <> "" Then
MyCollection.Add Item:="1", Key:=Cell.Text
End If