Keeping First Row Rather Than Last Row in Delete Duplicate M

jwasten

Board Regular
Joined
May 29, 2002
Messages
90
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Janet,

try

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

instead of

ActiveCell.EntireRow.Delete

Henk
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,219,006
Messages
6,145,711
Members
450,635
Latest member
Rookie3510

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top