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

[tonywatsonhelp]

HI Everyone,

I have this Macro that removes duplicates great

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

 

[Oeldere]

I don't have the (working) VBA code for that, but

maybe you can work from below to the top with removing duplicates.

 

[footoo]

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,.

 

[Peter_SSs]

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
	A	B
1	Hdr 1	Hdr 2
2	9c	9
3	9	9
4	10c	10
5	10	9
6	10c	10
Remove Duplicates Failure

 

[footoo]

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

	A	B

<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

 

[Peter_SSs]

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.

 

[tonywatsonhelp]

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

 

[footoo]

I can't change the order or add another column as the file is quite large

The size of the file does not prevent using a temporary helper column.

 

[tonywatsonhelp]

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)

 

[footoo]

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.