Back coloring an entire row if a duplicate is found in a dynamic range 1

[dgillz]

I need to determine if the current row (iRow), column A, is duplicated anywhere in the range of A11:A & Range("E7").Value.

E7 will contain a value that will indicate the end of the range. This could realistically be from 11 to maybe 10,000. It will change every time.

The SetGLRRowColor() sub creates the desired back color and is working fine.

So to reiterate, I need to replace my 1=1 with some logic to determine if the value in column A in that row (iRow), is a duplicate anywhere in the range. I can't work with just a true/false result that duplicates exist, I need a true/false result and the line number, one row at a time.

Here is my code:

'check for duplicate customer numbers
 For iRow = 11 To Range("E7").Value
    If iRow = 11 Then
        'do nothing, duplicates not possible on first row
    Else
        If [highlight #FCE94F]1 = 1 Then 'need code here[/highlight]
            Call SetGLRRowColor("YES", "Duplicate Account", iRow)
        Else
            Call SetGLRRowColor("NO", "", iRow)
        End If
    End If
Next iRow

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[strongm]

Have you had a look at conditional formatting for setting the colour?

 

[dgillz]

I have, but for various complicated reason, I need code to make my example above work.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[strongm]

Yes, and you can automate conditional formatting with VBA, e.g

[COLOR=green]' Simple example, just sets colour of duplicates in column a[/color]
Sub Example()

    Dim CondFormat As FormatConditions
    
    Set CondFormat = Range("$A$11:$A" & Range("E7").Value).FormatConditions
    
    With CondFormat
        If .Count > 0 Then .Delete [COLOR=green]' clean up existoing conditional formatting applied to range[/color]
        .Add xlUniqueValues
        .Item(1).DupeUnique = xlDuplicate
        .Item(1).Interior.Color = RGB(255, 0, 0)
    End With

End Sub

 

[dgillz]

Strongm,

Will that highlight the entire row? Or just the cell?

I need the entire row highlighted. I have the code to do that with my SetGLRRowColor() subroutine. But I do not know how to return a true/false on duplicates and the line number.

Any ideas?

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[mintjulep]

Application.Worksheetfunction.Countif(you can figure out what goes here) > 1

 

[combo]

If for iRow=11 'do nothing': maybe start from 12?

Is the data in column A sorted? If yes, then you can build a single loop and test if value in row i equals to values in row i-1 or i+1, if yes - this row has duplicates, otherwise no.

If the data is not sorted, I would pick the data in range to variant array (v=Range("A11:A" & Range("E7")) create helper array with duplicate marks (double loop required to test: For i=1 to N and, inside, For j=i+1 to N. Skip marked as duplicated items found in loop with i), and finally loop through the helper array and to format corresponding rows.

combo

 

[Andrzejek]

Or, move back a step. or two...
The best way to prevent duplicates is to not allowed them to happen in the first place.

Just my opinion.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>Will that highlight the entire row?

No, just the cell - but, as I say, it was just a simple example. here's a fractionally more complicated version that does entire row ...

[COLOR=blue]Sub Example2()

    Dim CondFormat As FormatConditions
    
    Set CondFormat = Range("$A$11:$C" & Range("E7").Value).EntireRow.FormatConditions
    
    With CondFormat
        If .Count > 0 Then .Delete [COLOR=green]' clean up existing conditional formatting applied to range[/color]
        .Add xlExpression, , "=COUNTIF($A$11:$A$" & Range("E7") & ",$A11)>1"
        .Item(1).Interior.Color = RGB(255, 255, 0)
    End With

End Sub[/color]

 

[dgillz]

Solved.

FWIW here was my solution:

'check for duplicates
iRow = 11
Do While Cells(iRow, 1).Value <> ""
    If iRow = 11 Then
        'do nothing, duplicates not possible on first row
    Else
        If Application.WorksheetFunction.CountIf(Range(Cells(11, 1), Cells(Range("E7").Value, 1)), Cells(iRow, 10)) > 1 Then
            Call SetGLRRowColor("NO", "Duplicate Old Account", 11)
            Call SetGLRRowColor("NO", "Duplicate Old Account", iRow)
        Else
            Call SetGLRRowColor("YES", "", iRow)
        End If
    End If
    iRow = iRow + 1
Loop

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Andrzejek]

Like combo said, why not

[green]'check for duplicates[/green]
iRow = 1[highlight #FCE94F]2[/highlight]
Do While Cells(iRow, 1).Value <> ""
    If Application.WorksheetFunction.CountIf(Range(Cells(11, 1), Cells(Range("E7").Value, 1)), Cells(iRow, 10)) > 1 Then
        Call SetGLRRowColor("NO", "Duplicate Old Account", 11)
        Call SetGLRRowColor("NO", "Duplicate Old Account", iRow)
    Else
        Call SetGLRRowColor("YES", "", iRow)
    End If
    iRow = iRow + 1
Loop

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>my solution:

Couple of questions/thoughts

1) You are calling

[tt]Call SetGLRRowColor("NO", "Duplicate Old Account", 11)[/tt]

for every duplicate found. By which I mean if you had

1
1

then it would be called twice. And in the extreme case that 10000 (your top level estimate) all happened to be duplicates, this would be called 10000 times. Is that really what you want to do?

2) And

[tt] 'do nothing, duplicates not possible on first row[/tt]

Well, see previous question.

[tt]Call SetGLRRowColor("NO", "Duplicate Old Account", 11)[/tt]

is doing the same thing as

[tt]Call SetGLRRowColor("NO", "Duplicate Old Account", iRow)[/tt]

if iRow was 11.

3) Have you checked performance with 10000 rows? You are iterating each line ...


Just some food for thought

 

[dgillz]

I actually noticed that and cleaned it up. It was something residual that I had left there by accident.

And yes, I want to check all 10,000 lines. A simple "there are duplicates" message does not help.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[strongm]

>And yes, I want to check all 10,000 lines

yes, I understood that (and the potential conditional formatting solution does that without a VBA loop). It was setting the exact same line 10000 times that seemed off

 

[combo]

VBA is fast enough. This code below processed 10k cells with worst case - no duplicates, in 5 seconds:

Sub test()
Dim i As Integer, j As Integer
Dim iMin As Integer, iMax As Integer
Dim vRange As Variant, bMarker() As Boolean
Debug.Print Now()
vRange = ThisWorkbook.Worksheets(1).Range("A1:A10000")
iMin = 1
iMax = UBound(vRange, 1)
ReDim bMarker(iMin To iMax)
For i = iMin To iMax
    If bMarker(i) = False Then
        For j = i + 1 To iMax
            If vRange(i, 1) = vRange(j, 1) Then
                bMarker(i) = True
                bMarker(j) = True
            End If
        Next j
    End If
Next i
With ThisWorkbook.Worksheets(1)
    For i = iMin To iMax
        If bMarker(i) Then .Rows(i).Interior.ColorIndex = 3
    Next i
End With
Debug.Print Now()
End Sub

combo

 

[strongm]

Not completely convinced that your example is a good analogue for what the OP's code. And 5 seconds seems a long time to me!

 

[dgillz]

5 seconds is fine compared to how long it would take to key this in to the ERP manually.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[strongm]

The conditional format is pretty much instantaneous ... (but may not do all that you think you want)

 

[SkipVought]

So what data does SetGLRRowColor() produce beyond the conditional formatting? Your SetGLRRowColor() function is a Black Box.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!