Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
dgillz

dgillz

Instructor
Mar 2, 2001
10,052
US
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:
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:
 
Sort by date Sort by votes
Have you had a look at conditional formatting for setting the colour?

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

Code: 
[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

 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
Application.Worksheetfunction.Countif(you can figure out what goes here) > 1
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
>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 ...

Code: 
[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]
 
Upvote 0 Downvote
Solved.

FWIW here was my solution:

Code: 
'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:
 
Upvote 0 Downvote
Like combo said, why not [ponder]

Code: 
[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
 
Upvote 0 Downvote
>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
 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
>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
 
Upvote 0 Downvote
VBA is fast enough. This code below processed 10k cells with worst case - no duplicates, in 5 seconds:
Code: 
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
The conditional format is pretty much instantaneous ... (but may not do all that you think you want)
 
Upvote 0 Downvote
So what data does SetGLRRowColor() produce beyond the conditional formatting? Your SetGLRRowColor() function is a Black Box.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Getting results from a dynamic dimension array
Replies
2
Views
717
Andrzejek
Andrzejek
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
591
sal21
sal21
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
647
remeng
remeng
IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
546
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top