CODEWITHSUNDEEP
excelvba
IFeelYourPain
IFeelYourPain

Reputation: 1

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

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.

Upvotes: 0

Views: 2647

Answers (1)

Shiva
Shiva

Reputation: 20935

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

Upvotes: 1

Related Questions