Remove Duplicates Leaves the wrong data I need last row not first?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,210
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
HI Everyone,

I have this Macro that removes duplicates great

Code:
Sub Macro2()
    ActiveSheet.Unprotect
    ActiveSheet.Range("$A:$N").RemoveDuplicates Columns:=Array(2, 9, 10), _
        Header:=xlYes
       ActiveSheet.Protect
End Sub
The only problem is it leaves the first row not the last row of data

can I get it to leave the last row?

thanks

Tony
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I don't have the (working) VBA code for that, but

maybe you can work from below to the top with removing duplicates.
 
Upvote 0
Maybe add a numerical sequence in a helper column, sort by that column in descending order, delete duplicates, sort by the helper column in ascending order, delete the helper column,.
 
Upvote 0
Just a comment: If it is important that duplicates are removed accurately, particularly if your data is large where you wouldn't notice any errors, then I would not rely on Excel's built-in Remove Duplicates feature. To see why, put this data on a fresh worksheet and use Remove Duplicates on it.

Excel Workbook
AB
1Hdr 1Hdr 2
29c9
399
410c10
5109
610c10
Remove Duplicates Failure
 
Upvote 0
Just a comment: If it is important that duplicates are removed accurately, particularly if your data is large where you wouldn't notice any errors, then I would not rely on Excel's built-in Remove Duplicates feature. To see why, put this data on a fresh worksheet and use Remove Duplicates on it.

Remove Duplicates Failure

AB

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Hdr 1[/TD]
[TD="align: center"]Hdr 2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]9c[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]10c[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]10c[/TD]
[TD="align: center"]10[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

I think it works properly if the data is first sorted into sequence
 
Upvote 0
I think it works properly if the data is first sorted into sequence
That may be the case (I don't know for sure) but that might not always suit the user to alter the order of their data, and could be cumbersome if lots of columns are involved. I haven't noticed anything in any Remove Duplicates documentation that says the data must be sorted on the columns you want assessed for duplicates. I just don't trust it given that I have seen it fail on several occasions.
 
Upvote 0
Thanks everyone,
I can't change the order or add another column as the file is quite large, Remove duplicates has worked great it just it leave the wrong row sound like its more trouble than I thought! but any ideas would be great.

thanks

Tony
 
Upvote 0
Footoo, I just said I can't change the order so what is the point of a helper column? (P.S. I feel this is going off track! so please only reply if you have a solution or something I will find helpful as I have to keep restarting my PC)
 
Upvote 0
Footoo, I just said I can't change the order so what is the point of a helper column? (P.S. I feel this is going off track! so please only reply if you have a solution or something I will find helpful as I have to keep restarting my PC)

The helper column will not change the order - it will only reverse the order temporarily.
I've already provided you with a solution in post #3 . What's the point of my posting if you don't bother to read it properly?
I leave it to you to write the code.
Please don't worry - I shall not be posting to this thread again.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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