Keeping First Row Rather Than Last Row in Delete Duplicate M

[jwasten]

I have a macro (thanks to Calypso) which deletes duplicate rows when the data in both Col A & Col B are the same as the following row. This macro keeps the bottom duplicate row. In other words, if I have Rows 1-5 that are duplicates, it keeps Row 5 rather than Row 1. I need the top row (in this case, Row 1) kept to be displayed rather than the bottom one. Is there a way to adjust the following macro so it keep the top row?

Range("A1").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1) Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

Beep

End Sub

Thanks!

Janet

 

[rikrak]

Janet,

try

ActiveCell.Offset(1, 0).EntireRow.Delete

instead of

ActiveCell.EntireRow.Delete

Henk

 

[jwasten]

PERFECT.

Thank goodness for Mr. Excel.com and its users!!

 

[Russell Hauf]

Hi Janet,

When deleting rows, it's a good idea to go from the bottom up - when you delete a row, the next row moves up and takes the place of the deleted row. For example, if you delete row 5, row 6 becomes row 5. Anyway, here is another way to do what you want:<pre><font color='#000000'><font color='#000080'>Sub</font> DeleteDups()<font color='#008000'>' Deletes rows in which the first 2 columns have the same</font><font color='#008000'>' data as the first 2 columns in the row above - so a</font><font color='#008000'>' delete duplicates that keeps the first row.</font><font color='#000080'>Dim</font> intRow<font color='#000080'>As</font><font color='#000080'>Integer</font><font color='#000080'>Dim</font> intFirstRow<font color='#000080'>As</font><font color='#000080'>Integer</font><font color='#000080'>Dim</font> intLastRow<font color='#000080'>As</font><font color='#000080'>Integer</font><font color='#000080'>Dim</font> intCol<font color='#000080'>As</font><font color='#000080'>Integer</font>


intFirstRow = 1
intCol = 1
intLastRow = Cells(65536, intCol).End(xlUp).Row<font color='#000080'>For</font> intRow = intLastRow To intFirstRow Step -1<font color='#000080'>If</font> Cells(intRow, intCol) = Cells(intRow - 1, intCol) And _
Cells(intRow, intCol + 1) = Cells(intRow - 1, intCol + 1)<font color='#000080'>Then</font>
Cells(intRow, intCol).EntireRow.Delete<font color='#000080'>End</font><font color='#000080'>If</font><font color='#000080'>Next</font> intRow<font color='#000080'>End</font><font color='#000080'>Sub</font></font></pre>
This message was edited by Russell Hauf on 2002-08-23 13:04

 

[jwasten]

THANKS!