Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use autofilter to delete duplicate rows (2nd criteria) ?
I have this code already that deletes rows based on criteria "3/25/2005" in column A. And it works exellently. 1. Is there a way to add functionality to this autofilter code to delete duplicate rows by checking column A for duplicate values and deleting the row? 2. I would like to add a second criteria to delete row with "12/31/2005" in column A. How do i make this happen? Here is what i have: Any ideas will be mucho appreciated ;) Code: -------------------- Worksheets("MyTable").Select With ActiveSheet If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter .Range("A1").AutoFilter Field:=1, Criteria1:=#3/25/2005# .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _ (xlCellTypeVisible).EntireRow.Delete .AutoFilterMode = False End With -------------------- -- Mslady ------------------------------------------------------------------------ Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776 View this thread: http://www.excelforum.com/showthread...hreadid=480312 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use autofilter to delete duplicate rows (2nd criteria) ?
Mslady,
Add a second criteria with the or condition - both sets of values (dates) will be selected .Range("A1").AutoFilter Field:=1, Criteria1:=#3/25/2005#, _ Operator:=xlOr, Criteria2:=#12/31/2005# not sure what you mean about duplicates and deletions, the code will already select all duplicate values and delete them - are you asking how to keep the first value and delete the rest ? -- Cheers Nigel "Mslady" wrote in message ... I have this code already that deletes rows based on criteria "3/25/2005" in column A. And it works exellently. 1. Is there a way to add functionality to this autofilter code to delete duplicate rows by checking column A for duplicate values and deleting the row? 2. I would like to add a second criteria to delete row with "12/31/2005" in column A. How do i make this happen? Here is what i have: Any ideas will be mucho appreciated ;) Code: -------------------- Worksheets("MyTable").Select With ActiveSheet If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter .Range("A1").AutoFilter Field:=1, Criteria1:=#3/25/2005# .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _ (xlCellTypeVisible).EntireRow.Delete .AutoFilterMode = False End With -------------------- -- Mslady ------------------------------------------------------------------------ Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776 View this thread: http://www.excelforum.com/showthread...hreadid=480312 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use autofilter to delete duplicate rows (2nd criteria) ?
Thanks Nigel, that works fine. :) I am asking how to delete the entire row if column A has more than one (duplicates). And keep one. e.g If column A has more than one count like this: 6/20/2005 6/20/2005 6/20/2005 etc. How to delete the rest and keep just one. Looking for a way to do this via AUTOFILTER. Thanks Nigel ;) -- Mslady ------------------------------------------------------------------------ Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776 View this thread: http://www.excelforum.com/showthread...hreadid=480312 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete duplicate rows, keep one | Excel Discussion (Misc queries) | |||
delete duplicate rows | Excel Worksheet Functions | |||
delete rows autofilter | Excel Programming | |||
HELP delete duplicate rows. | Excel Programming | |||
Delete duplicate rows | Excel Programming |