Tom’s Tutorials For Excel: Deleting or Keeping Rows With a Multiple Criteria Array
Here’s a “this way or that way” pair of macros that use an array to hold a set of items to determine which rows you want to keep or delete. In the picture, an original list has clothing items in column A that are accompanied by various colors of those items in column B.
Both macros hold the same array items of “Red”, “White”, and “Blue”. The macro named KeepOnlyArrayColors
keeps all rows where “Red”, “White”, or “Blue” are found in column B, while deleting all the other rows. The macro named DeleteArrayColors
does the opposite: it deletes all rows where “Red”, “White”, or “Blue” are found in column B, but keeps all the other rows.
Sub KeepOnlyArrayColors()
Application.ScreenUpdating = False
Dim LastRow&, rng As Range
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("B2:B" & LastRow)
Dim ColorList As Variant, ColorItem As Variant
ColorList = Array("Red", "White", "Blue")
For Each ColorItem In ColorList
rng.Replace What:=ColorItem, Replacement:=ColorItem & "|", LookAt:=xlWhole
Next ColorItem
rng.AutoFilter Field:=1, Criteria1:="<>*|"
On Error Resume Next
rng.SpecialCells(12).EntireRow.Delete
Err.Clear
rng.Replace What:="|", Replacement:="", LookAt:=xlPart
Set rng = Nothing
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
Sub DeleteArrayColors()
Application.ScreenUpdating = False
Dim LastRow&, rng As Range
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("B2:B" & LastRow)
Dim ColorList As Variant, ColorItem As Variant
ColorList = Array("Red", "White", "Blue")
For Each ColorItem In ColorList
rng.Replace What:=ColorItem, Replacement:="", LookAt:=xlWhole
Next ColorItem
On Error Resume Next
rng.SpecialCells(4).EntireRow.Delete
Err.Clear
Set rng = Nothing
Application.ScreenUpdating = True
End Sub
This is great. I stumbled on this while looking at something else. Could this be easily modified to use logical statements? I need to remove lines with values below 0.100 in one column and Lines with values with less than six months to go in another.
Thanks in advance!
Thanks for stopping by my Excel blog.
Sounds like a perfect job for AutoFilter with an AND operator, meaning you filter for the column with the values for those below 0.100 AND for the column with (presumably) dates that are less than 6 months from TODAY. Filter for that, select the visible rows (those rows which met the criteria you want to delete) below your header row. With those rows selected, hit the F5 key, click the Special button, select Visible Cells Only and click OK. Finally, delete the selected visible-only rows by hitting Ctrl+- (that is, the Ctrl key and the minus or dash key). Select “Entire row”, click OK, then remove the AutoFilter and you are done. All easily do-able with a macro if this is a recurring task.