Require macro

R

Ron99

Active Member
Joined
Feb 10, 2010
Messages
347
Office Version
  1. 2016
Platform
  1. Windows
Hello,

PN's in each row are alternate to each other but they are repeated in other rows in different order / sequence

Example - PN's in 1st row are also in row 3,5 & 7.

Output requirement - I need to retain only 1 row which has the maximum alternate parts and delete the remaining rows. In this case it should retain Row7 and delete Row 1,3 & 5. The same should happen to B PN's and so on.

[TABLE="width: 288"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: #44546A"]PN
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT1
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT 2
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT3
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT4
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT5
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A1
[/TD]
[TD="class: xl65, bgcolor: transparent"]A2
[/TD]
[TD="class: xl65, bgcolor: transparent"]A3
[/TD]
[TD="class: xl65, bgcolor: transparent"]A4
[/TD]
[TD="class: xl65, bgcolor: transparent"]A5
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B1
[/TD]
[TD="class: xl65, bgcolor: transparent"]B2
[/TD]
[TD="class: xl65, bgcolor: transparent"]B3
[/TD]
[TD="class: xl65, bgcolor: transparent"]B4
[/TD]
[TD="class: xl65, bgcolor: transparent"]B5
[/TD]
[TD="class: xl65, bgcolor: transparent"]B6
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A2
[/TD]
[TD="class: xl65, bgcolor: transparent"]A1
[/TD]
[TD="class: xl65, bgcolor: transparent"]A3
[/TD]
[TD="class: xl65, bgcolor: transparent"]A4
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B2
[/TD]
[TD="class: xl65, bgcolor: transparent"]B1
[/TD]
[TD="class: xl65, bgcolor: transparent"]B3
[/TD]
[TD="class: xl65, bgcolor: transparent"]B4
[/TD]
[TD="class: xl65, bgcolor: transparent"]B5
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A3
[/TD]
[TD="class: xl65, bgcolor: transparent"]A4
[/TD]
[TD="class: xl65, bgcolor: transparent"]A5
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B3
[/TD]
[TD="class: xl65, bgcolor: transparent"]B1
[/TD]
[TD="class: xl65, bgcolor: transparent"]B2
[/TD]
[TD="class: xl65, bgcolor: transparent"]B4
[/TD]
[TD="class: xl65, bgcolor: transparent"]B5
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A4
[/TD]
[TD="class: xl65, bgcolor: transparent"]A5
[/TD]
[TD="class: xl65, bgcolor: transparent"]A1
[/TD]
[TD="class: xl65, bgcolor: transparent"]A2
[/TD]
[TD="class: xl65, bgcolor: transparent"]A3
[/TD]
[TD="class: xl65, bgcolor: transparent"]A6
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

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.
Sort by date Sort by votes
If you simply want to delete all rows where col F is empty, try
Code: 
Sub DelRws()
   Columns(6).SpecialCells(xlBlanks).EntireRow.Delete
End Sub
 
Upvote 0
Thank you, just another quick one. How about deleting duplicate lines incase there is no blanks. The sequence is not the same but should identify duplicates in another row and delete. Incase if the data was this way. PN is the main source and if any duplicates identified in another row should delete that row A1 appears in row 3, 5 & 7. These rows to be deleted. same should apply for B1 and so on. Thanks for your help!

[TABLE="width: 240"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: #44546A"]PN
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT1
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT 2
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT3
[/TD]
[TD="class: xl66, width: 64, bgcolor: #44546A"]ALT4
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]A1
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A2
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A3
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A4
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A5
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]B1
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B2
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B3
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B4
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B5
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]A2
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A1
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A3
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A4
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A5
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]B2
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B1
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B3
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B4
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B5
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]A3
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A4
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A5
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A1
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A2
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]B3
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B1
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B2
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B4
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B5
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]A4
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A5
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A1
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A2
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A3
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
How about
Code: 
Sub DelRws()
   
   Dim Cl As Range
   Dim Cnt As Long
   Dim Qty As Long
   Dim Rng As Range
   Dim Fnd As Range
   
   Set Rng = Range("A1").CurrentRegion
   Columns(Rng.Columns.Count).SpecialCells(xlBlanks).EntireRow.Delete
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Qty = WorksheetFunction.CountIf(Rng, Cl.Value)
      If Qty > 1 Then
         For Cnt = 1 To Qty - 1
            Set Fnd = Rng.Find(Cl.Value, Cl, , , , , True, , False)
            Fnd.EntireRow.ClearContents
         Next Cnt
      End If
   Next Cl
   
End Sub
 
Upvote 0
Hello,

I really appreciate your time and help on this one. I did run the code but unfortunately showing error on this line

Columns(Rng.Columns.Count).SpecialCells(xlBlanks).EntireRow.Delete
 
Upvote 0
How about
Code: 
Sub DelRws()
   
   Dim Cl As Range
   Dim Cnt As Long
   Dim Qty As Long
   Dim Rng As Range
   Dim Fnd As Range
   
   Set Rng = Range("A1").CurrentRegion
   On Error Resume Next
   Columns(Rng.Columns.Count).SpecialCells(xlBlanks).EntireRow.Delete
   On Error GoTo 0
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Qty = WorksheetFunction.CountIf(Rng, Cl.Value)
      If Qty > 1 Then
         For Cnt = 1 To Qty - 1
            Set Fnd = Rng.Find(Cl.Value, Cl, , , , , True, , False)
            Fnd.EntireRow.ClearContents
         Next Cnt
      End If
   Next Cl
   
End Sub
 
Upvote 0
Bulls-eye!!! amazing...This has saved tons of time thank you very much.. before you leave I have one last request..

I want the code to lookup the entire row matching any other row and delete that row.

Example - code can delete Row 3 & Row 7 as it matches with Row 1 even though it is not of the same order but it is same alpha numeric in a different sequence. This time the code should not delete Row 5 as all the alpha numeric does not match all. This should apply for the rest of the table as it I not limited to only these rows and columns.


[TABLE="width: 179"]
<colgroup><col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <col width="41" style="width: 31pt; mso-width-source: userset; mso-width-alt: 1499;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <tbody>[TR]
[TD="class: xl66, width: 35, bgcolor: #44546A"]PN[/TD]
[TD="class: xl66, width: 37, bgcolor: #44546A"]ALT1[/TD]
[TD="class: xl66, width: 42, bgcolor: #44546A"]ALT 2[/TD]
[TD="class: xl66, width: 40, bgcolor: #44546A"]ALT3[/TD]
[TD="class: xl66, width: 41, bgcolor: #44546A"]ALT4[/TD]
[TD="class: xl66, width: 42, bgcolor: #44546A"]ALT5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A1[/TD]
[TD="class: xl65, bgcolor: transparent"]A2[/TD]
[TD="class: xl65, bgcolor: transparent"]A3[/TD]
[TD="class: xl65, bgcolor: transparent"]A4[/TD]
[TD="class: xl65, bgcolor: transparent"]A5[/TD]
[TD="class: xl65, bgcolor: transparent"]A6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B1[/TD]
[TD="class: xl65, bgcolor: transparent"]B2[/TD]
[TD="class: xl65, bgcolor: transparent"]B3[/TD]
[TD="class: xl65, bgcolor: transparent"]B4[/TD]
[TD="class: xl65, bgcolor: transparent"]B5[/TD]
[TD="class: xl65, bgcolor: transparent"]B6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A2[/TD]
[TD="class: xl65, bgcolor: transparent"]A1[/TD]
[TD="class: xl65, bgcolor: transparent"]A3[/TD]
[TD="class: xl65, bgcolor: transparent"]A4[/TD]
[TD="class: xl65, bgcolor: transparent"]A5[/TD]
[TD="class: xl65, bgcolor: transparent"]A6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B2[/TD]
[TD="class: xl65, bgcolor: transparent"]B1[/TD]
[TD="class: xl65, bgcolor: transparent"]B3[/TD]
[TD="class: xl65, bgcolor: transparent"]B4[/TD]
[TD="class: xl65, bgcolor: transparent"]B5[/TD]
[TD="class: xl65, bgcolor: transparent"]B6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A3[/TD]
[TD="class: xl65, bgcolor: transparent"]A4[/TD]
[TD="class: xl65, bgcolor: transparent"]A5[/TD]
[TD="class: xl65, bgcolor: transparent"]A7[/TD]
[TD="class: xl65, bgcolor: transparent"]A8[/TD]
[TD="class: xl65, bgcolor: transparent"]A1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B3[/TD]
[TD="class: xl65, bgcolor: transparent"]B1[/TD]
[TD="class: xl65, bgcolor: transparent"]B2[/TD]
[TD="class: xl65, bgcolor: transparent"]B4[/TD]
[TD="class: xl65, bgcolor: transparent"]B5[/TD]
[TD="class: xl65, bgcolor: transparent"]B8[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A4[/TD]
[TD="class: xl65, bgcolor: transparent"]A5[/TD]
[TD="class: xl65, bgcolor: transparent"]A1[/TD]
[TD="class: xl65, bgcolor: transparent"]A2[/TD]
[TD="class: xl65, bgcolor: transparent"]A3[/TD]
[TD="class: xl65, bgcolor: transparent"]A6[/TD]
[/TR]
</tbody>[/TABLE]

Thank you once again and advance happy new year!!

Thanks,
Ron..
 
Upvote 0
I'm afraid I don't know how to do that.
 
Upvote 0
Thank you for all the help....

Anyone who can help me out ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Excel formula please
Replies
4
Views
90
JoeMo
J
M
  • Solved
Get Cell Difference in Array SCAN/LAMBDA
Replies
2
Views
289
MrRajKumar
M
A
  • Question Question
Can someone help find the Excel formula
Replies
5
Views
271
Cubist
Cubist
J
  • Solved
My Two Calculators Don't Match
Replies
1
Views
249
Peter_SSs
Peter_SSs
R
  • Solved
Create a 2 column sheet that moves the selected cell along then down when filled
Replies
2
Views
340
R11989
R

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

Share this page

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