Bulk row deletion

[Guest]

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.

 

[Guest]

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