Is it a duplicate row?

[Guest]

I am trying to determine if a row is a duplicate based on two criteria. This
is what I have so far, but it doesn't work. Any pointers much appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C match AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" & iLastRow),
..Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub

 

[Bob Phillips]

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=" & .Cells(i,
"C").Value & ")," & _
"--(J1:J" & iLastRow & "=" & .Cells(i,
"J").Value & "))") > 1 Then
' do some stuff here
Else ' do something else
End If
Next i

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thanks Bob.

I'm getting a type mismatch error on the "evaluate" line. Could it be
because column J contains dates?

 

[Guest]

Is your file sorted either ascending or descendingorder? If so maybe you can
use this one:

Sub DupeDel()
Set currentCell = Worksheets(YourSheetNumber).Range("C1")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value And
currentCell.Offset(0, 7).Value = currentCell.Offset(1, 7).Value Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Loop
End Sub

It only works on files that are sorted, but if you do not want to delete the
files you can modify it to count the occurences.

 

[Bob Phillips]

Could well be.

Try this version, I have also assumed that column C is test not numeric

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=""" & .Cells(i,
"C").Value & """)," & _
"--(J1:J" & iLastRow & "=--""" &
Format(.Cells(i, "J").Value, "yyyy-mm-dd") & """))") > 1 Then
' do some stuff here
Else ' do something else
End If
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Hi Bob,

I'm sorry that I couldn't reply sooner...

The code below works wonderfully, & I really appreciate your help!

 

[Bob Phillips]

Not a problem. Glad it helped.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Martin]

I was about to ask the same question thanks for the help.

Martin