View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Deleting AutoFiltered Rows

ActiveSheet.AutoFilter.Range.EntireRow.Delete
Activesheet.Range(Activesheet.Autofilter.Range.Add ress).EntireRow.Delete

For me (and as I would expect)
Both worked the same in Excel 2002
Only the visible rows (including the header row) were deleted.

--
Regards,
Tom Ogilvy


"keepITcool" wrote in message
t.com...
Tom

hmm.... I'm stupefied and very surprised.

Let's analyse the behaviour of the delete method

Assume a1:b10 hold data. row1 is header, row 4 is visible.
(make sure you have a copy of the data in a20)

?debug.print activesheet.autofilter.range.address :
$a$1:$b$10

activesheet.autofilter.entirerow.delete
deletes ONLY visible rows (row 1 and row 4)

whereas
Range(activesheet.autofilter.range.address).entire row.delete
deletes the entire range.

MOST UNUSUAL & VERY ILLOGICAL??!!

the autofilter.range should give a range object. pure and simple.
if I apply a delete method to a range it should delete that range.

It appears to me that the delete method tracks back the call stack,
find that it wasn't called by a "disconnected" range, but by the
autofilter range and then decides to delete the visible cells only.


Do you know more of these "sly" interpretations?



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, _
Criteria1:="<A000000000", _
Operator:= rator:=xlAnd
with activesheet.autofilter.range
.offset(1,0).resize(.rows.count -1).EntireRow.Delete
End With
Selection.AutoFilter
Range("A1").Select