How to match strings in cells on Excel, with if/or operators, and delete the rows

Question

Disclaimer: I've never used Visual Basic and I've never made a macro.

I am trying to create a macro in Microsoft Excel 2010 that will delete all rows where neither column G nor column I contain the string "Ohio", "Indiana", or "Kentucky". To clarify, the row should be deleted if the cell does not contain either of those three state names. I want the macro to start at row 6, as rows 1-5 contain vital information. The whole sheet contains over 14000 rows and only ~1.5% of those are actually helpful.

Also, I am looking to be able to reuse this macro, but for other terms (besides Ohio, Indiana, and Kentucky) in other columns (besides G and I).

It may also help me if you can, besides correcting this, explain what exactly I am saying in these lines. Perhaps in Java terms, or Python is okay too. Not necessary, but may help.

Sub DeleteIfNotKYINOH()
Dim i, LastRow
LastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = LastRow To 6 Step -1

I get a type mismatch error on the next line.

If Cells(i, "G").Value = "Ohio" Or "Indiana" Or "Kentucky" Then
Cells(i, "G").Value = True
End If
If Cells(i, "I").Value = "Ohio" Or "Indiana" Or "Kentucky" Then
Cells(i, "I").Value = True
End If
If Cells(i, "G").Value Or Cells(i, "I").Value = False Then
Cells(i, "G").EntireRow.Delete
End If
Next
' DeleteIfNotKYINOH Macro
' Delete all rows that do not contain Ohio, Indiana, or Kentucky, as a state.
'

'
End Sub

Your type mismatch error is due to the `Or` format. Each `Or` needs to be it's own statement that evaluates to `True` or `False` like this: `If Cells(i, "G").Value = "Ohio" Or Cells(i, "G").Value = "Indiana" Or Cells(i, "G").Value = "Kentucky" Then` — Automate This, Apr 20 '14 at 23:38

David Zemens · Accepted Answer · 2014-04-21T12:48:01.483

There are a few things to consider, it looks like you are on the right track, though, you even got the backwards iteration over the collection (this stumps a lot of people!).

Make sure to declare your variables properly (i and LastRow should probably be Long integer, not unspecified Variant type).

If statements can include Or joins, but have to be like this:

If Cells(i, "G").Value = "Ohio" Or Cells(i, "G").Value  = "Indiana" Or Cells(i, "G").Value  = "Kentucky"

Since you want to be able to re-use the macro for other strings, of course you could go in and edit each instance of "Ohio" or "Indiana", etc., but that can be tedious and error-prone.

You could do something like this instead to re-use it for a list of any number of states, just change the assignment to the states variable.

Const states as String = "Ohio,Indiana,Kentucky"
Sub TestDeleteIfNot()
Dim i as Long, LastRow as Long
Dim cl as Range

LastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = LastRow To 6 Step -1
    With Cells(i, "G")
        If Not(InList(.Value, states)) And Not(InList(.Offset(0,2).Value, states))
            .EntireRow.Delete
        End If
    End With
Next

End Sub

This routine calls on a function InList which accepts as its arguments two strings. The first string should be the value being compared, and the second is a comma-delimited "list" of allowable values.

Function InList(strVal as String, list as String) As Boolean
Dim a as Variant
For each a in Split(list, ",")
    If strVal = a Then
        InList = True
        Exit For
    End If
Next
End Function

The function converts the list to an array and iterates that against the compare value. It should return False if the value is not found. So then the logic in the calling sub runs this on cells in COlumn G and also Column I, only deleting the row if BOTH tests return False.

Plus one for extra mile of explanation and walk through. Btw, why create a string and then a function. Wouldn't it take less time to pass the list directly to `Array` then use `Application.Match`? — L42, Apr 21 '14 at 02:08
@L42 from my POV (and probably most people) it's easier for a human to instantiate a string (and then use the `Split` function to convert it to an array), rather than to dimension an array and specify each item. But that may be just personal preference. As for the iteration versus the `Match` function, see Tim's response [HERE](http://stackoverflow.com/a/18769246/1467082). — David Zemens, Apr 21 '14 at 02:16
Cool. I'm curious as well that I asked. :) Btw, when I say array, I mean like this `arr = Array("Ohio","Indiana","Kentucky")` where `arr` is declared as variant. — L42, Apr 21 '14 at 02:36
@L42 Ahhh yeah you could do that or you could simply send `Array("Ohio","Indiana","Kentucky")` to the function, etc. Just depends on your needs I guess. For me, this sort of thing is *usually* a constant definition list, but since you can't declare arrays as Constant, I use the `As String` method and `Split` to an array. I'm not saying the other way is wrong, only that this is how I would typically approach it. Cheers. — David Zemens, Apr 21 '14 at 13:34

How to match strings in cells on Excel, with if/or operators, and delete the rows

1 Answers1