VBA remove all duplicate rows

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,354
Office Version
  1. 2010
VBA remove all duplicate rows </SPAN></SPAN>

Hello,</SPAN></SPAN>

I would like to remove all duplicate rows find within 1 to 14 columns</SPAN></SPAN>

Original with duplicates rows marked in red</SPAN></SPAN>

Book1
CDEFGHIJKLMNOP
1n1n2n3n4n5n6n7n8n9n10n11n12n13n14
22
32
43
53
63
74
85
95
106
117
1222
1323
1424
1525
1625
1726
1827
1922
2023
2125
2226
2323
2423
2524
2625
2744
2845
2946
3047
3146
3247
3355
3456
3556
3657
3767
38222
39223
40224
41224
42225
43225
44226
45227
462223
472223
482223
492223
502223
512224
522224
5322233
5422233
5522233
5622233
5722234
5822234
5922235
6022235
61222333
62222333
63222333
642223333
652223333
662223334
672223334
682223335
692223335
7022334567
7122334567
7222334556
7322334557
74222333334
75222333334
76222333335
77222333336
78222333337
79222333344
803333445567
813333445567
8222233333445
8322233333445
8422233333446
8522233333447
86223333344556
87223333344557
88223333344567
89223333344567
90223333445567
91233333445567
92233333445567
932223333344556
942223333344557
952223333445567
962233333445567
972233333445567
982233333445567
9922233333445567
Sheet6


Required without duplicates</SPAN></SPAN>

Book1
CDEFGHIJKLMNOP
1n1n2n3n4n5n6n7n8n9n10n11n12n13n14
22
33
44
55
66
77
822
923
1024
1125
1226
1327
1444
1545
1646
1747
1855
1956
2057
2167
22222
23223
24224
25225
26226
27227
282223
292224
3022233
3122234
3222235
33222333
342223333
352223334
362223335
3722334567
3822334556
39222333334
40222333335
41222333336
42222333337
433333445567
4422233333445
4522233333446
4622233333447
47223333344556
48223333344557
492223333344556
502223333344557
5122233333445567
Sheet7



Thank you all</SPAN></SPAN>
Excel 2000</SPAN></SPAN>
Regards</SPAN></SPAN>,</SPAN>
Moti</SPAN></SPAN>
</SPAN></SPAN>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
.
Code:
Option Explicit


Sub DeleteRows()
Dim Rng As Range
    With ActiveSheet
        Set Rng = Range("C1", Range("P1").End(xlDown))
        Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    End With


End Sub
 
Upvote 0
Please note the following bit in Logit's pose (I didn't test it, but I suspect the array argument may need to be different).

Code:
Option Explicit


Sub DeleteRows()
Dim Rng As Range
    With ActiveSheet
        Set Rng = Range("C1", Range("P1").End(xlDown))
        Rng[COLOR=#0000ff][B].RemoveDuplicates [/B][/COLOR]Columns:=Array(1, 2), Header:=xlYes
    End With

End Sub

But the main point is that there is a built-in tool on the Data menu that will do this for you. Why bother with VBA at all?
 
Upvote 0
Hello Logit, </SPAN></SPAN>
Hello Greg Truby,</SPAN></SPAN>

Thank you for the help

Code gives error 438 and stop at line below</SPAN></SPAN>
Code:
Rng[COLOR=#0000ff][B].RemoveDuplicates [/B][/COLOR]Columns:=Array(1, 2), Header:=xlYes

Regards</SPAN></SPAN>,</SPAN>
Moti</SPAN></SPAN>
 
Upvote 0
Are you going to be doing this time-after-time-after-time, so you need a macro? Or is this just a once-in-a-while thing? If so, I'd just do it manually using the button on the DATA tab.
 
Upvote 0
Are you going to be doing this time-after-time-after-time, so you need a macro? Or is this just a once-in-a-while thing? If so, I'd just do it manually using the button on the DATA tab.
Hello Greg Truby, yes I need to use frequently that is why I need VBA solution, would be nice if unique list (no duplicate) could be copied another location or in the sheet and reaming the originals too.

Regards,
Moti
 
Upvote 0
Last edited:
Upvote 0
.
I agree.

I am not familiar with the requirements of programming VBA for Excel 2000. So sorry.

Perhaps someone else will be able to assist you.
 
Upvote 0
.
I agree.

I am not familiar with the requirements of programming VBA for Excel 2000. So sorry.

Perhaps someone else will be able to assist you.
Logit, yes I understand you, it is a very old version.</SPAN></SPAN>

I want to thank you for your help.
</SPAN></SPAN>

Regards.
</SPAN></SPAN>
Moti
</SPAN></SPAN>
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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