Removing almost duplicate rows

Check my solution.

Please Login or Register  to view this content.

This is another code that checks all 13 columns. Couple of points

1. The code could be shorter but I'm sorting before running the actual code to ensure that incase the row that doesn't have the word "NEW" comes first (& sorting after to return original sort)
2. If column "N" doesn't have "NEW" it has a space & not blank
3. I assume that there's no duplicate row with the word "NEW" more than once

Please Login or Register  to view this content.

Unorthodox Method Perhaps...

Please Login or Register  to view this content.

Just a side note...Not sure if you noticed the discrepancy in your result file...B4 & E11 values differ from values in example after rows are deleted...Not sure how that happened...

How could I copy and edit this macro to remove all rows where column X (#24) = ND or PD? Or is there a easier macro?

Originally Posted by gz3s36

But not sure about (Value = " ").

I guess you're referring to my code which contains Value = " " … If you want to include ND or PD, you could write it like below

Please Login or Register  to view this content.

macro to remove all rows where column X (#24) = ND or PD?

Another Option if the above quote is all you want...

Please Login or Register  to view this content.

Thanks for rep + ... Glad we could contribute...

Nankw83: This worked great for one file but it doesn't for another. The only changes I made are P1 to N1, (x, 14 to x, 16) and Sheet1 to sheet2. Any ideas? it stops on .UsedRange.Resize(, lCol + 1).Sort Key1:=Range("N1"), Order1:=xlDescending, Header:=xlYes

Please Login or Register  to view this content.

I suggest when you post you address the correct member...i.e.

@ nankw83

With reference to your code in Post # etc etc etc

Originally Posted by gz3s36

Nankw83: ... Any ideas? it stops on .UsedRange.Resize(, lCol + 1).Sort Key1:=Range("N1"), Order1:=xlDescending, Header:=xlYes

Ok, I have added option explicit & assign some ranges to the ws … Give the below code a try & see if it works. If it still doesn't work, then we need to see a sample file

Please Login or Register  to view this content.

Here is my test file.

You need to convert the table to range in order for my code to work. To do that, click anywhere in the table then from the Ribbon click Table Design then Convert to Range

Also I noticed that in the below line you change the 13 to 100 however, your table has only 56 columns

Please Login or Register  to view this content.

It wasn't in a table so the tab wasn't showing up.