Bulk row deletion
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.
|