delete duplicate rows based on two column names

[boiboi]

Hi,

Can anyone help with below? My code is running for around 5 minutes or more with data of only 10 records in the worksheet.

Dim nameCol As Range
Dim aCell As Range
Dim Cell As Range
Dim Cel As Range, N&
N = 0


Set nameCol = .Range("A1:AU1").Find("apple")
Set aCell = .Range("A1:AU1").Find("orange")

Application.Union(nameCol, aCell).EntireColumn.Select

For Each Cell In Selection
If Cell <> Empty Then
For Each Cel In Selection
If Cel <> Empty And _
Cel.Value = Cell.Value And _
Cel.Address <> Cell.Address Then
Cel.EntireRow.Delete
N = N + 1
End If
Next Cel
End If
Next


Much appreciated in advance.

 

[boiboi]

https://www.dropbox.com/s/lf58u8kusydbitt/fruits.xlsm?dl=0

 

[Fluff]

How about

Sub boiboi()
   Dim Fnd1 As Range, Fnd2 As Range
   
   Set Fnd1 = Range("1:1").Find("apple", , , xlWhole, , , False, , False)
   Set Fnd2 = Range("1:1").Find("orange", , , xlWhole, , , False, , False)
   If Fnd1 Is Nothing Then Exit Sub
   If Fnd2 Is Nothing Then Exit Sub
   ActiveSheet.UsedRange.RemoveDuplicates Array(Fnd1.Column, Fnd2.Column), xlYes
End Sub

 

[boiboi]

when i tried to apply it only on sheet2, it doesn't remove duplicates.

https://www.dropbox.com/s/p0dbwro8bbziwki/fruits2.xlsm?dl=0

 

[Fluff]

In what way doesn't it work?

 

[boiboi]

let me clarify further.

if i were to run it after copy-pasting information from sheet1, there seems to be an error as attached.

https://www.dropbox.com/s/p0dbwro8bbziwki/fruits2.xlsm?dl=0

appreciate your kind help, Fluff.

 

[Fluff]

Try

   With TargetWS
      Set Fnd1a = .Range("1:1").Find("apple", , , xlWhole, , , False, , False)
      Set Fnd2a = .Range("1:1").Find("orange", , , xlWhole, , , False, , False)
      If Fnd1a Is Nothing Then Exit Sub
      If Fnd2a Is Nothing Then Exit Sub
      .UsedRange.RemoveDuplicates Array(Fnd1a.Column, Fnd2a.Column), xlYes
    End With

 

[boiboi]

hi fluff,

if i wanted to highlight the duplicate rows, instead of removing them, is there any way to go about it?

 

[Fluff]

That's what the code in post#8 does.

 

[boiboi]

Thank you for your kind reply. Let me explain.

The code in post#8 did not give correct results as explained in post#9 & 10. Further illustrated in attachment.

https://www.dropbox.com/s/lf58u8kusydbitt/fruits.xlsm?dl=0

 

[Fluff]

Using Sheet1 from the file supplied in post#15 I get

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	kiwi	grape	mango	apple	durian	pineapple	tomato	orange	coconut
2	abc	def	abc	1		abc		a	abc
3	abc	def	abc	2		abc		b	abc
4	abc	def	abc	3		abc		a	abc
5	abc	def	abc	4		abc		b	abc
6	abc	def	abc	5		abc		a	abc
7	abc	def	abc	5		abc		b	abc
8	abc	def	abc	5		abc		@	abc
9	abc	def	abc	5		abc		@	abc
10	abc	def	abc	5		abc		@	abc
11	abc	def	abc	5		abc		@	abc
12
13	abc	def	abc	13		abc		b	abc
14	abc	def	abc	14		abc		b	abc
15	abc	def	abc	14		abc		@	abc
16	abc	def	abc	15		abc		@	abc
17	abc	def	abc	18		abc		b	abc
18	abc	def	abc	19		abc		b	abc
19	abc	def	abc	20		abc		a	abc
20	abc	def	abc	22		abc		b	abc
21	abc	def	abc	5		abc		@	abc
22	abc	def	abc	5		abc		@	abc
Sheet1

in what way is that wrong?