Highlights Duplicate Entries

[krishhi]

hello Folks, I have written a macro to highlight duplicates and it works good. But i don't want to highlight the empty cell. My macro shows and highlights the next blank as duplicate. How do i overcome this issue.

Below is my code


Sub IDENTIFY_duplicates()

Dim i As Integer, j As Integer
Dim lr As Integer
Dim n As String
n = InputBox("Enter the column to Delete Duplicates")
lr = ActiveSheet.UsedRange.Rows.Count

For i = 1 To lr
For j = i + 1 To lr

If Range(n & j).Value = Range(n & i).Value Then
Range(n & j).Interior.ColorIndex = 4
End If

Next
Next
MsgBox ("Done")

End Sub

Any Help?

Krrish

[mikerickson]

One loop is sufficiant.
For i = 1 To lr
With Range(n & i)
If (Application.CountIf(Range(n & ":" & n), .Value) > 1) And (.Value <> vbNullString) Then
.Interior.ColorIndex = 4
End If
End With
Next

[p45cal]

Sub IDENTIFY_duplicates()

Dim i As Integer, j As Integer
Dim lr As Integer
Dim n As String
n = InputBox("Enter the column to Delete Duplicates")
lr = ActiveSheet.UsedRange.Rows.Count

For i = 1 To lr
For j = i + 1 To lr
If Range(n & j).Value <> Empty Then
If Range(n & j).Value = Range(n & i).Value Then
Range(n & j).Interior.ColorIndex = 4
End If
End If
Next
Next
MsgBox ("Done")

End Sub

[krishhi]

wow... Very Quick Replies,

Thank you very much guys.:friends:

[p45cal]

One loop is sufficiant.
For i = 1 To lr
With Range(n & i)
If (Application.CountIf(Range(n & ":" & n), .Value) > 1) And (.Value <> vbNullString) Then
.Interior.ColorIndex = 4
End If
End With
Next

Not quite the same, mikerickson..:whip
Yours highlights all duplicates whereas krishni's only highlights duplicates after the first instance - this might be important if users are subsequently deleting highlighted rows/cells.

[krishhi]

Not quite the same, mikerickson..:whip
Yours highlights all duplicates whereas krishni's only highlights duplicates after the first instance - this might be important if users are subsequently deleting highlighted rows/cells.


Yes You are right p45cal

Thanks for the Solution.

[Simon Lloyd]

Why use VBA at all? Excel does this easily using conditional formatting and with less overhead or performance issues, although conditional formatting is by its very nature a volatile function.

Give your data range a dynamic name (see here for how to create a dynamic named range http://www.contextures.com/xlNames01.html )
Highlight your data, then from the menubar FORMAT>CONDITIONAL FORMATTING, choose formula is and enter this =COUNTIF(MyRange,B2)>1, for an explanation on highlighting duplicates look here http://www.cpearson.com/excel/Duplicates.aspx

[krishhi]

Why use VBA at all? Excel does this easily using conditional formatting and with less overhead or performance issues, although conditional formatting is by its very nature a volatile function.

Give your data range a dynamic name (see here for how to create a dynamic named range http://www.contextures.com/xlNames01.html )
Highlight your data, then from the menubar FORMAT>CONDITIONAL FORMATTING, choose formula is and enter this =COUNTIF(MyRange,B2)>1, for an explanation on highlighting duplicates look here http://www.cpearson.com/excel/Duplicates.aspx

Hi Simon, :hi:

Ya I know there is conditional formating tool in excel. But i want to go through the macro. why Because i am learning vba. :cool:

[mikerickson]

Not quite the same, mikerickson..:whip
Yours highlights all duplicates whereas krishni's only highlights duplicates after the first instance - this might be important if users are subsequently deleting highlighted rows/cells.
This should fix that.
If (Application.CountIf(Range(n & "1:" & n & i), .Value) > 1) And (.Value <> vbNullString) Then

[krishhi]

Hello again,,

I have another problem here, When the row contains the same string like "krish" in A1 and "KRISH" in A8. The Macro Only considers they are unique values. It doesn't show the second one as duplicate.

Any Idea?

:help

[p45cal]

mikerickson' amendment should work as it's not case sensitive.
For mine use:
If UCase(Range(n & j).Value) = UCase(Range(n & i).Value) Then instead of
If Range(n & j).Value = Range(n & i).Value Then

[krishhi]

mikerickson' amendment should work as it's not case sensitive.
For mine use:
If UCase(Range(n & j).Value) = UCase(Range(n & i).Value) Then instead of
If Range(n & j).Value = Range(n & i).Value Then


Thanks for the reply, I want to know why this code taken as ucase and lcase as different.

Any Clear Help on this? :(

[p45cal]

Thanks for the reply, I want to know why this code taken as ucase and lcase as different.

Any Clear Help on this? :(
I don't understand the question. Is it working or not? You could use lcase or ucase, as long as you use the same one on each side of the = sign.

Clear Help will be provided on a Clear Question

[krishhi]

I don't understand the question. Is it working or not? You could use lcase or ucase, as long as you use the same one on each side of the = sign.

Clear Help will be provided on a Clear Question

Let me explain clearly. Why my macro doesn't work with Lower case words and upper case words combination. like,
for example:
in a1 : krish
in a5: KRISH.

When i run my macro it says there is no duplicate. then we need to add ur code i.e. lcase and ucase.

[p45cal]

When i run my macro it says there is no duplicate. then we need to add ur code i.e. lcase and ucase. The code

If Range(n & j).Value = Range(n & i).Value Then

has the equals sign as a comparator, KrisHHi is not equal to krishhi, so the condition returns FALSE.
However,
UCASE("KrisHHi") is "KRISHHI", and
UCASE("krishhi") is "KRISHHI". These are the same so the comparison returns TRUE.

You could use LCASE instead of UCASE, in which case both would be krishhi.

[krishhi]

The code

If Range(n & j).Value = Range(n & i).Value Then

has the equals sign as a comparator, KrisHHi is not equal to krishhi, so the condition returns FALSE.
However,
UCASE("KrisHHi") is "KRISHHI", and
UCASE("krishhi") is "KRISHHI". These are the same so the comparison returns TRUE.

You could use LCASE instead of UCASE, in which case both would be krishhi.

Oh, atlast i got it, thank you very much. p4scal. By the way where can i get vba complete msdn. do u have any idea?