Highlight Duplicate Rows (In Series)
Thank you, Patrick! I appreciate your help.
"Patrick Molloy" wrote:
Option Explicit
Sub GetDuplicates()
Dim text As String
Dim cols As Long
Dim index As Long
Dim lastrow As Long
cols = 10 ' number of columns to check
lastrow = Range("A1").End(xlDown).Row
'add a column to get unique contants for say 10 columns
Columns(1).Insert
'built the text contetns
For index = 1 To cols
text = text & "& RC" & index + 1
Next
'then place in column 1
With Range("A1")
.FormulaR1C1 = "=" & Mid(text, 2)
.AutoFill Destination:=Range(Range("A1"), Cells(lastrow, "A"))
End With
' now add another column for the counter
Columns(1).Insert
With Range("A1")
.Formula = "=COUNTIF(B1:B" & lastrow & ",B1)"
.AutoFill Destination:=Range(Range("A1"), Cells(lastrow, "A"))
End With
'clean up
With Range(Range("A1"), Cells(lastrow, "A"))
.Calculate
.Value = .Value
End With
Columns(2).Delete
End Sub
"Rugby Stud" wrote in message
...
Hi Patrick,
Sounds promising... how do I do it? :-)
If I want to concat the data in range ?1:?5 and then use countif()?
"Patrick Molloy" wrote:
probably add a column which concatenates the data from the used cells in
a
row - depends on how many columns you have
you could then use the countif() formula to highlight where the
duplicates
are ( countif 1)
"Rugby Stud" wrote in message
...
I'm looking for a way to highlight duplicate rows that cover a series
of
cells. For example, each row in A1:A5 must match each row in A55:E55
to
be
considered a duplicate.
What's the best way to go about this?
|