comparing rows

[Papa Jonah]

Is there a macro that can be used to compare a row to the row above resulting
in a highlight if it is a duplicate?
What I need is to identify rows that are cell for cell duplicates - meaning
that if EVERY cell in the row is identical to the one above.

TIA
Papa

 

[sbitaxi]

This ill do it for you, so long as every cell in the range match,
including number of used columns

Sub DupRows()
Dim MyCell As Range
Dim RowCols As Integer
Dim DupCount As Integer
Dim RowSel As Integer

RowSel = 2
Do Until RowSel = ActiveSheet.UsedRange.Rows.Count
Range("A" & RowSel & ":" & Cells(RowSel,
Columns.Count).End(xlToLeft).Address).Select
DupCount = 0
RowCols = Cells(ActiveCell.Row,
Columns.Count).End(xlToLeft).Column

For Each MyCell In Selection
If MyCell.Value = MyCell.Offset(-1, 0).Value Then
DupCount = DupCount + 1
End If
Next MyCell

If DupCount = RowCols Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If

RowSel = RowSel + 1
Loop
End Sub

Good luck!

Steven

 

[sbitaxi]

Correction (the web browser wraps this in an odd way. If you copy and
paste the code without correction line breaks, it won't work). Try
this -

Sub DupRows()
Dim MyCell As Range
Dim RowCols As Integer
Dim DupCount As Integer
Dim RowSel As Integer


RowSel = 2
Do Until RowSel = ActiveSheet.UsedRange.Rows.Count
Range("A" & RowSel & ":" & Cells(RowSel, _
Columns.Count).End(xlToLeft).Address).Select
DupCount = 0
RowCols = Cells(ActiveCell.Row,
Columns.Count).End(xlToLeft).Column


For Each MyCell In Selection
If MyCell.Value = MyCell.Offset(-1, 0).Value Then
DupCount = DupCount + 1
End If
Next MyCell


If DupCount = RowCols Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If


RowSel = RowSel + 1
Loop
End Sub

 

[Alan Beban]

Is there a macro that can be used to compare a row to the row above resulting
in a highlight if it is a duplicate?
What I need is to identify rows that are cell for cell duplicates - meaning
that if EVERY cell in the row is identical to the one above.

TIA
Papa

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=RowsEqual(rng1,rng2) will return True or False

Alan Beban