Deleting AutoFiltered Rows
 

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

Deleting AutoFiltered Rows
 

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 :

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

Deleting AutoFiltered Rows
 

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

--
Regards,
Tom Ogilvy

"Nirmal Singh" wrote in
message ...
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

Deleting AutoFiltered Rows
 

On Fri, 4 Feb 2005 08:03:58 -0500, "Tom Ogilvy" wrote:


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


Thanks guys. Both of those suggestions work fine.

Nirmal

Deleting AutoFiltered Rows
 

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

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

Deleting AutoFiltered Rows
 

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 :

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

Deleting AutoFiltered Rows
 

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

Deleting AutoFiltered Rows
 

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.Add ress).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.

Deleting AutoFiltered Rows
 

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 :

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

Deleting AutoFiltered Rows
 

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


"Nirmal Singh" wrote in
message ...
On Fri, 4 Feb 2005 08:03:58 -0500, "Tom Ogilvy" wrote:


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


Thanks guys. Both of those suggestions work fine.

Nirmal

Deleting AutoFiltered Rows
 

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

"keepITcool" wrote in message
t.com...
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.Add ress).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.