Excel VBA delete row if contains certain strings or has a delete flag set

Question

I'm trying to search Column B for 3 different strings. I need it to search for city, state, and zip, and if any of the columns have matching city, state, or zips, then I need that row removed.

I also have it set up to remove a row if there is a 0 at the beginning of a field in column D, but I couldn't get this to work either.

Here is the code I have thus far:

Sub Removal()
Dim i As Long

For i = Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
If Left(Range("D" & i), 1) = "0" Then
Rows(i).Delete
Else
Select Case LCase(Range("B" & i))
Case Is = "Orlando", "FL", "37941"
Rows(i).Delete
End Select
End If
Next i
End Sub

The code is not doing anything though.

You should consider using an autofilter instead of looping through the range. It's much more efficient. Take a look at the following to get an idea of how it works: stackoverflow.com/a/16901714/138938 — Jon Crowell
– Jon Crowell, Commented Oct 31, 2014 at 21:50

Shiva · Accepted Answer · 2014-10-31 22:01:43Z

1

It's because you are doing LCase on the values in Column B, but comparing to TitleCase ("Orlando") and UpperCase "FL" words in your Case statement.

Modify your code like so. I tested it on my local Excel and it works.

UPDATE I also modified the code to handle the cases you've mentioned in the comments here.

Sub Removal()
    Dim i As Long, searchString As String

        For i = Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
            ' if a row is marked for deletion, delete it and continue.
          If Left(Range("D" & i), 1) = "0" Then
            Rows(i).Delete
              ' skip to next row
            GoTo NextRow
          End If

        searchString = LCase(Range("B" & i))

        If (InStr(1, searchString, "orlando") > 0) Or _
            (InStr(1, searchString, "fl") > 0) Or _
            (InStr(1, searchString, "37941") > 0) Then
            Rows(i).Delete
        End If

NextRow:
        Next i

End Sub

BEFORE Running Code:

before

AFTER Running Code:

after

edited Oct 31, 2014 at 22:01

answered Oct 31, 2014 at 20:59

Shiva

21.1k15 gold badges88 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

IFeelYourPain Over a year ago

If I don't have any matches in column B is doesn't remove those with 0 in the beginning in column D? Also is there anyway to remove if it match at the beginning because some strings in column b would have like FL - 37941, but it won't remove those either. @Shiva

Shiva Over a year ago

This is a deviation from your original question :) I will update the code shortly.

IFeelYourPain Over a year ago

You are the greatest, thank you. Curious also, how would I do this across other pages to save more time? @Shiva

Shiva Over a year ago

Glad to hear. Please mark my answer as the answer then. => meta.stackexchange.com/a/5235/245495 Also, to do this across pages, make the sub parameterized to take in a Range object, and an array of things to search for and delete. I can show you a sample if you want.

IFeelYourPain Over a year ago

Yes I would appreciate that thanks. Also if there are no matches in column B it's still not removing matches from Column D. @Shiva

Collectives™ on Stack Overflow

Excel VBA delete row if contains certain strings or has a delete flag set

1 Answer 1

BEFORE Running Code:

AFTER Running Code:

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

BEFORE Running Code:

AFTER Running Code:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related