VBA remove all duplicate rows

[motilulla]

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
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
2	2
3	2
4	3
5	3
6	3
7	4
8	5
9	5
10	6
11	7
12	2	2
13	2	3
14	2	4
15	2	5
16	2	5
17	2	6
18	2	7
19	2	2
20	2	3
21	2	5
22	2	6
23	2	3
24	2	3
25	2	4
26	2	5
27	4	4
28	4	5
29	4	6
30	4	7
31	4	6
32	4	7
33	5	5
34	5	6
35	5	6
36	5	7
37	6	7
38	2	2	2
39	2	2	3
40	2	2	4
41	2	2	4
42	2	2	5
43	2	2	5
44	2	2	6
45	2	2	7
46	2	2	2	3
47	2	2	2	3
48	2	2	2	3
49	2	2	2	3
50	2	2	2	3
51	2	2	2	4
52	2	2	2	4
53	2	2	2	3	3
54	2	2	2	3	3
55	2	2	2	3	3
56	2	2	2	3	3
57	2	2	2	3	4
58	2	2	2	3	4
59	2	2	2	3	5
60	2	2	2	3	5
61	2	2	2	3	3	3
62	2	2	2	3	3	3
63	2	2	2	3	3	3
64	2	2	2	3	3	3	3
65	2	2	2	3	3	3	3
66	2	2	2	3	3	3	4
67	2	2	2	3	3	3	4
68	2	2	2	3	3	3	5
69	2	2	2	3	3	3	5
70	2	2	3	3	4	5	6	7
71	2	2	3	3	4	5	6	7
72	2	2	3	3	4	5	5	6
73	2	2	3	3	4	5	5	7
74	2	2	2	3	3	3	3	3	4
75	2	2	2	3	3	3	3	3	4
76	2	2	2	3	3	3	3	3	5
77	2	2	2	3	3	3	3	3	6
78	2	2	2	3	3	3	3	3	7
79	2	2	2	3	3	3	3	4	4
80	3	3	3	3	4	4	5	5	6	7
81	3	3	3	3	4	4	5	5	6	7
82	2	2	2	3	3	3	3	3	4	4	5
83	2	2	2	3	3	3	3	3	4	4	5
84	2	2	2	3	3	3	3	3	4	4	6
85	2	2	2	3	3	3	3	3	4	4	7
86	2	2	3	3	3	3	3	4	4	5	5	6
87	2	2	3	3	3	3	3	4	4	5	5	7
88	2	2	3	3	3	3	3	4	4	5	6	7
89	2	2	3	3	3	3	3	4	4	5	6	7
90	2	2	3	3	3	3	4	4	5	5	6	7
91	2	3	3	3	3	3	4	4	5	5	6	7
92	2	3	3	3	3	3	4	4	5	5	6	7
93	2	2	2	3	3	3	3	3	4	4	5	5	6
94	2	2	2	3	3	3	3	3	4	4	5	5	7
95	2	2	2	3	3	3	3	4	4	5	5	6	7
96	2	2	3	3	3	3	3	4	4	5	5	6	7
97	2	2	3	3	3	3	3	4	4	5	5	6	7
98	2	2	3	3	3	3	3	4	4	5	5	6	7
99	2	2	2	3	3	3	3	3	4	4	5	5	6	7
Sheet6

Required without duplicates</SPAN></SPAN>

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
2	2
3	3
4	4
5	5
6	6
7	7
8	2	2
9	2	3
10	2	4
11	2	5
12	2	6
13	2	7
14	4	4
15	4	5
16	4	6
17	4	7
18	5	5
19	5	6
20	5	7
21	6	7
22	2	2	2
23	2	2	3
24	2	2	4
25	2	2	5
26	2	2	6
27	2	2	7
28	2	2	2	3
29	2	2	2	4
30	2	2	2	3	3
31	2	2	2	3	4
32	2	2	2	3	5
33	2	2	2	3	3	3
34	2	2	2	3	3	3	3
35	2	2	2	3	3	3	4
36	2	2	2	3	3	3	5
37	2	2	3	3	4	5	6	7
38	2	2	3	3	4	5	5	6
39	2	2	2	3	3	3	3	3	4
40	2	2	2	3	3	3	3	3	5
41	2	2	2	3	3	3	3	3	6
42	2	2	2	3	3	3	3	3	7
43	3	3	3	3	4	4	5	5	6	7
44	2	2	2	3	3	3	3	3	4	4	5
45	2	2	2	3	3	3	3	3	4	4	6
46	2	2	2	3	3	3	3	3	4	4	7
47	2	2	3	3	3	3	3	4	4	5	5	6
48	2	2	3	3	3	3	3	4	4	5	5	7
49	2	2	2	3	3	3	3	3	4	4	5	5	6
50	2	2	2	3	3	3	3	3	4	4	5	5	7
51	2	2	2	3	3	3	3	3	4	4	5	5	6	7
Sheet7

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

 

[Logit]

.

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

 

[Greg Truby]

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

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?

 

[motilulla]

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>

Rng[COLOR=#0000ff][B].RemoveDuplicates [/B][/COLOR]Columns:=Array(1, 2), Header:=xlYes

Regards</SPAN></SPAN>,</SPAN>
Moti</SPAN></SPAN>

 

[Greg Truby]

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.

 

[Logit]

.
Here is a download link for my workbook. See if this works on your machine : https://www.amazon.com/clouddrive/share/jxb68cuvFtOX4HPThGA7wYUOA8IMkD8hUxdTozCEe2H

Greg is correct tho ... if this is just one time thing, the macro shouldn't be needed.

 

[motilulla]

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

 

[motilulla]

.
Here is a download link for my workbook. See if this works on your machine : https://www.amazon.com/clouddrive/share/jxb68cuvFtOX4HPThGA7wYUOA8IMkD8hUxdTozCEe2H

Greg is correct tho ... if this is just one time thing, the macro shouldn't be needed.

Hello Logit, I did download workbook, I cannot run it because I am using Excel 2000, uses .xls not .xlsm

I think version could be the reason your code is not working with me.

Regards,
Moti

 

[Logit]

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

 

[motilulla]

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