Bulk row deletion
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. |
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. |
All times are GMT +1. The time now is 12:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com