Tom’s Tutorials For Excel: Deleting or Keeping Rows With a Multiple Criteria Array

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