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

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

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