Deleting AutoFiltered Rows

[Nirmal Singh]

I am using the following code to filter a list and delete unwanted records.

The list has headers on the top row. These are also getting deleted. How can I do this
without deleting the header row?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>A000000000", Operator:= rator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Delete
Selection.AutoFilter
Range("A1").Select

Nirmal Singh

 

[keepITcool]

Nirmal,

using the intersect with 1 row offset works for me:

Sub FilterDel()
Dim rDel As Range
With Range("A1")
.AutoFilter
.AutoFilter 1, "<>A000000000"
With .Worksheet.AutoFilter.Range
On Error Resume Next
Set rDel = Intersect(.Cells.Offset(1), _
.SpecialCells(xlCellTypeVisible))
On Error GoTo 0
If Not rDel Is Nothing Then rDel.EntireRow.Delete
End With
.AutoFilter
.Select
End With
End Sub




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


Nirmal Singh wrote :

 

[Tom Ogilvy]

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

 

[Nirmal Singh]

with activesheet.autofilter.range
.offset(1,0).resize(.rows.count -1).EntireRow.Delete
End With

Thanks guys. Both of those suggestions work fine.

Nirmal

 

[keepITcool]

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).entirerow.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 :

 

[Tom Ogilvy]

ActiveSheet.AutoFilter.Range.EntireRow.Delete
Activesheet.Range(Activesheet.Autofilter.Range.Address).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.

 

[keepITcool]

tom,
cant reproduce me original observation ..
may have been erroneous.. got to go for now,
I'll get back if I can reproduce ... hmm.. confused!


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


Tom Ogilvy wrote :

 

[Peter T]

Hi KeepITcool

Do you know more of these "sly" interpretations?

Not sure about "sly interpretations" but Excel appears to treat only the
visible cells in a filtered range as a range, ignoring hidden rows. Similar
to Tom's delete method are Copy & Format, if you follow what I mean. This
contrasts with rows hidden in the normal way.

As you say, the VBA range object returns the entire range, clearly different
to the filtered range Excel works with. I think it's contigious - even if
it's not!

I'd love to get that Excel range, without using selectspecial visible cells.

Regards,
Peter T

 

[keepITcool]

Tom,

Done some more testing...

If autofilter is active the delete method will delete:
specified rows if ALL those rows are either visible or hidden,
but if the specified range contains a mix of hidden and visible rows,
then only the visible rows will be deleted.



Sub test()
Fill
[3:9].Delete
[a1].AutoFilter

MsgBox "Deleted 3:9" _
& vbLf & "Mixed visibility => Visible rows 4,6,8 deleted"

Fill
[3:3,5:5,7:7,9:9].Delete
[a1].AutoFilter
MsgBox "Deleted 3,5,7,9" _
& vbLf & "All rows hidden => specified rows deleted"

Fill
[3:3,5:5,7:7,9:10].Delete
[a1].AutoFilter
MsgBox "Deleted 3,5,7,9:10" _
& vbLf & "Mixed => only the visible row(10) in spec deleted"

End Sub

Sub Fill()
Cells.Clear
[a1:b1] = Array("Number", "Even")
[a2:a11] = [ROW(2:11)]
[b2:b11] = [INDEX(MOD(ROW(2:11),2)=0,0,1)]
[a1].AutoFilter
[a1].AutoFilter 2, True
End Sub




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


Tom Ogilvy wrote :

 

[keepITcool]

I just posted some observations in a reply to Tom.

Apparently if the visibility of rows is "mixed" the delete method will
get "sly", but if all rows are invisible it will delete the invisible
rows.. Never knew that... and i find it very dangerous.


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


Peter T wrote :

 

[Jim May]

Before your line:
Selection.SpecialCells(xlCellTypeVisible).Delete
enter
selection.offset(1, 0).resize(selection.rows.count - 1, 1).select
FILE-SAVE before trying...
HTH

 

[Tom Ogilvy]

If all rows are visible then that would be the expected/desired behavior -
all rows met the critiera (or you could check that there is actually
criteria applied).

In the case of no rows meeting the criteria (only the header row visible),
that has bitten me several times and I usually put in code to test for that.

In the mixed case, that is the desired behavior.
--
Regards,
Tom Ogilvy

Tom,

Done some more testing...

If autofilter is active the delete method will delete:
specified rows if ALL those rows are either visible or hidden,
but if the specified range contains a mix of hidden and visible rows,
then only the visible rows will be deleted.



Sub test()
Fill
[3:9].Delete
[a1].AutoFilter

MsgBox "Deleted 3:9" _
& vbLf & "Mixed visibility => Visible rows 4,6,8 deleted"

Fill
[3:3,5:5,7:7,9:9].Delete
[a1].AutoFilter
MsgBox "Deleted 3,5,7,9" _
& vbLf & "All rows hidden => specified rows deleted"

Fill
[3:3,5:5,7:7,9:10].Delete
[a1].AutoFilter
MsgBox "Deleted 3,5,7,9:10" _
& vbLf & "Mixed => only the visible row(10) in spec deleted"

End Sub

Sub Fill()
Cells.Clear
[a1:b1] = Array("Number", "Even")
[a2:a11] = [ROW(2:11)]
[b2:b11] = [INDEX(MOD(ROW(2:11),2)=0,0,1)]
[a1].AutoFilter
[a1].AutoFilter 2, True
End Sub




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


Tom Ogilvy wrote :

ActiveSheet.AutoFilter.Range.EntireRow.Delete
Activesheet.Range(Activesheet.Autofilter.Range.Address).EntireRow.Dele
te

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