Deleting rows which match filter

[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)?

 

[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.

 

[Cecilkumara Fernando]

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

HTH
Cecil

 

[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

 

[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

 

[Tom Ogilvy]

Bob Flanagan has the right idea, but the solution suggested would not work
unless Plan A was in the first row of data and in the first column of the
filter range (but you are filtering on the 5th column).

Selection.AutoFilter Field:=5, Criteria1:="Plan A"
Set rng1 = ActiveSheet.AutoFilter.Range
if rng1.columns(1).SpecialCells(xlvisible).count > 1 then
Set rng1 = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1)
rng1.EntireRow.Delete
end if

 

[Nikki]

Excellent. Thanks very much, Tom. This has solved the problem.

Regards
Nikki
===========