Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing rows
There are quite a few ways to do this. Do you know the length of each row
(number of columns)? Will it be the same number of columns every time? Papa Jonah wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing rows
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 On Aug 7, 10:02*am, Papa Jonah wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing rows
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing rows
Papa Jonah wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing rows | Excel Programming | |||
Comparing rows | Excel Programming | |||
comparing rows and deletion | Excel Programming | |||
Comparing rows in three sheets | Excel Programming | |||
Comparing two rows | Excel Programming |