Is there a macro to delete an entire row if a duplicate entry appears only in a certain column.
1. Look for the column header with the name "File Number"
2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column.
This is regardless of what is contained in the other columns.
For example..let's say these cells contained this data...
B1 - UTE00225
B2 - UTE00546
B3 - UTE65513
B4 - UTE00225
B5 - UTE00225
In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1.
I'm using Excel 2003.
P.S. I don't really have code here, so I'm not sure if I should've wrapped it or not. On the flip side, I was also afraid of a forum rule violation if I DID wrap it and shouldn't have. If I should have wrapped any portion of my message, please let me know and I'll be sure to fix it.
Thanks much.
Last edited by duugg; 02-25-2009 at 07:00 PM.
this will look at the cells in column B row by row, deleting rows with duplicate values, until it gets to a blank cell in column B:
Code:Sub test() r = 1 Do Do While Application.WorksheetFunction.CountIf(ActiveSheet.Range("B:B"), Cells(r, 2)) > 1 ActiveSheet.Range("B:B").Find(Cells(r, 2), Cells(r, 2)).EntireRow.Delete Loop r = r + 1 Loop Until Cells(r, 2) = "" End Sub
Dusty,
That worked perfectly! Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks