Deleting rows which match filter

F

Fred Smith

I want to delete all the rows which match a selected filter. Currently I
have:

Selection.AutoFilter Field:=6, Criteria1:="0"
Rows("2:1517").Delete Shift:=xlUp

It works great as long as I don't have more than 1517 rows. How do I change
it so I specify all data rows in the worksheet (same as an end,down would
do)?
 
T

Tom Ogilvy

Selection.AutoFilter Field:=6, Criteria1:="0"
set rng = Activesheet.Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
rng.Delete Shift:=xlup

we need to shift down one row so we don't delete the header row.
 
C

Cecilkumara Fernando

Fred Smith,
Try,
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) _
..SpecialCells(xlVisible).Delete Shift:=xlUp

HTH
Cecil
 
N

Nikki

Tom

This works fine except when the autofilter result is empty. Then the
code seems to delete all rows in the worksheet.

For example, I used the code below to search for and delete rows with
"Plan A" in column E leaving the header row untouched. This works if
at least one row has "Plan A" in column E however with no "Plan A" in
column E the code deletes all rows in the worksheet - though not the
header.

Selection.AutoFilter Field:=5, Criteria1:="Plan A"
Set rng1 = ActiveSheet.AutoFilter.Range
Set rng1 = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1)
rng1.EntireRow.Delete

Is there a way to avoid all rows being deleted when the autofilter
returns a nil result?

Thanks
Nikki
 
B

Bob Flanagan

Nikki, you can put an if test in before the delete statement:

If rng1.Cells(1).value2 = "Plan A" then
rng1.EntireRow.Delete
end if

You need to put Option Compare Text at the top of the module so that the
above test is case insensitive.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
Top