Marco Help
 

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,A2 1,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.

Marco Help
 

Hi,

here's one way:

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


--
Hope that helps.

Vergel Adriano


"LaDdIe" wrote:

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,A2 1,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.

Marco Help
 

Spot on,

Thanks Vergel,

"Vergel Adriano" wrote:

Hi,

here's one way:

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


--
Hope that helps.

Vergel Adriano


"LaDdIe" wrote:

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,A2 1,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.