Is there a way to use autofilter in code to show only certain rows with many
criteria?

For example, I need to display and delete only those rows conforming to all
of the following:

Column A: Cells like "###.###.####.######.###.##.###"
Columns C, D, E: Abs(C) + Abs(D) + Abs(E) = 0
Column F: < "U"

Once displayed, these rows will then be deleted. Your example code would be
most appreciated.

FYI: my original sheet contains 54,102 rows by 22 columns. About 29,000 rows
will be deleted. Thanks in advance.

Hi Quartz,

You need to UNION your data ranges, look up UNION in the Excel Help, but the
code is something like

Set xls = Application.ActiveSheet
Set rgeSelection = Application.Selection

For Each rgeCell In rgeSelection
If xls.Cells(rgeCell.Row, rgeEnd.column + 2).Value Then
If rgeDelete Is Nothing Then
Set rgeDelete = rgeCell
Else
Set rgeDelete = Union(rgeDelete, rgeCell)
End If
End If
Next rgeCell

rgeDelete.EntireRow.Delete

The line "If xls.Cells(rgeCell.Row, rgeEnd.column + 2).Value Then" in my
sheet the last visible column plus two more columns, (a hidden column),
contained a value of True or False which permitted the user to delete the row
or not. You can change this to something like "If rgeCell.Row.Visible Then"

Hope that helps


"quartz" wrote:

Is there a way to use autofilter in code to show only certain rows with many
criteria?

For example, I need to display and delete only those rows conforming to all
of the following:

Column A: Cells like "###.###.####.######.###.##.###"
Columns C, D, E: Abs(C) + Abs(D) + Abs(E) = 0
Column F: < "U"

Once displayed, these rows will then be deleted. Your example code would be
most appreciated.

FYI: my original sheet contains 54,102 rows by 22 columns. About 29,000 rows
will be deleted. Thanks in advance.