Don's suggestion looks for consecutive rows that are duplicated.
This doesn't care if the rows are consecutive. Make sure you save yourdata
before you test -- it destroys the original.
And I'm confused about your original post. You loop through the bottomused row
of column C to row 1. But you use C10:C## in the countif range.
I'm gonna guess that this was a typo in your code and you're looking at all the
rows.
So this looks at all the data in column C one row at a time. If the value
appears in column C more than once, then it looks to see if there's a ZZ in
column E of any of the rows with that duplicated value.
If ZZ is found on any of those rows, then this procedure deletes all of those
duplicates.
So if we start with:
ABC AA
DEF BB
DEF ZZ
GHI CC
asdf DD
GHI CC
GHI ZZ
We'll finish with:
ABC AA
asdf DD
Both DEF's are deleted since ZZ was in E3.
All GHI's are deleted since ZZ was in E7.
Option Explicit
Sub testme()
Dim myColC As Range
Dim myColE As Range
Dim LastRow As Long
Dim wks As Worksheet
Dim myCountOfZZ As Long
Dim HowMany As Long
Dim iRow As Long
Dim DelRng As Range
Dim QtMarks As String
Dim myCVal As Variant
Dim myFormula As String
Set wks = ActiveSheet 'worksheets("Sheet1") '???
With wks
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set myColC = .Range("C1", .Cells(LastRow, "C"))
Set myColE = .Range("E1", .Cells(LastRow, "E"))
For iRow = LastRow To 1 Step -1
myCVal = .Cells(iRow, "C").Value
HowMany = Application.CountIf(myColC, myCVal)
If HowMany = 1 Then
'not a duplicate, so skip it
Else
If Application.IsNumber(myCVal) Then
QtMarks = ""
Else
QtMarks = """" 'surround strings with double quotes
End If
myFormula = "sumproduct(--(" & myColC.Address _
& "=" & QtMarks & myCVal & QtMarks & ")," _
& "--(" & myColE.Address & "=""zz""))"
myCountOfZZ = .Evaluate(myFormula)
If myCountOfZZ > 0 Then
If DelRng Is Nothing Then
Set DelRng = .Cells(iRow, "A")
Else
Set DelRng = Union(DelRng, .Cells(iRow, "A"))
End If
End If
End If
Next iRow
End With
If DelRng Is Nothing Then
'do nothing
Else
DelRng.EntireRow.Select
'use .delete when you're done testing!
'delrng.entirerow.select
End If
End Sub
The myFormula string looks like:
sumproduct(--($C$1:$C$20="ABC"),--($E$1:$E$20="zz"))
This is a way of counting the number of "ABC"'s in column C that have ZZ in
column E of the same row.
=sumproduct() likes to work with numbers. The -- stuff changes truesand falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail here:
http://www..xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
