Delete row based off multiple criteria

Question

I am trying to delete certain rows in my excel sheet, based on the value of a cell in row H. I am trying to write a code so if it finds the word "deleted" or "processing" "random".

Right now I have a code that will look through the entire sheet and delete the row based off of one value, but is there an easy way to code it so that it can look for more than one?

My current code:

Dim r As Long, endRow As Long, pasteRowIndex As Long

endRow = 100 ' of course it's best to retrieve the last used row number via a function

For r = 1 To endRow 'Loop through Open Orders and search for my criteria

If Cells(r, Columns("H").Column).Value = "VARIABLE" Then 'Found

        'Copy the current row
        Rows(r).Select
        Selection.Delete

    End If
 Next r

This works great if I am only looking for the one value "variable" but I can't seem to figure out how to make it search for more than one at a time.

Possible duplicate of [Efficient way to delete rows (Multiple criterias) VBA](http://stackoverflow.com/questions/26712724/efficient-way-to-delete-rows-multiple-criterias-vba) — Mathieu Guindon, Jan 12 '17 at 16:59

score 0 · Accepted Answer · answered Jan 12 '17 at 16:57

You can try this:

Sub foo()
Dim r As Long, endRow As Long, pasteRowIndex As Long

endRow = 100 ' of course it's best to retrieve the last used row number via a function

For r = endRow To 1 Step -1  'Loop through Open Orders and search for my criteria

    Select Case Cells(r, Columns("H").Column).Value
        Case "deleted", "processing", "random"
            Rows(r).Delete
        Case Else
            ' Do nothing...

    End Select

    End If
 Next r
End Sub

Note that I reversed your loop For r = endRow to 1 Step - 1. This is usually necessary when deleting items from a collection, otherwise you have to re-index, which makes code harder to read & debug.

This is a case-sensitive match as currently implemented, so it's looking only for all lower-case. I can modify if needed.

score 0 · Answer 2 · answered Jan 12 '17 at 17:00

The problem is that when you are running any loop that involved deleting entire rows, you must run the loop BACKWARDS using Step -1.

Dim r As Long, endRow As Long, pasteRowIndex As Long, somevar as String

endRow = Range("H" & Rows.Count).End(xlUp).Row

For r = endRow to 1 Step -1
    somevar = Range("H" & r).Value        
    If somevar = "VARIABLE" or somevar = "processing" or somevar = "Random" Then 'Found
        Rows(r).Delete
    End If
Next r

Delete row based off multiple criteria

2 Answers2