0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
IFeelYourPain
  • 1
  • 2
  • 6
  • 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: http://stackoverflow.com/a/16901714/138938 – Jon Crowell Oct 31 '14 at 21:50

1 Answers1

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

Shiva
  • 20,575
  • 14
  • 82
  • 112
  • 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 – IFeelYourPain Oct 31 '14 at 21:23
  • This is a deviation from your original question :) I will update the code shortly. – Shiva Oct 31 '14 at 21:47
  • You are the greatest, thank you. Curious also, how would I do this across other pages to save more time? @Shiva – IFeelYourPain Oct 31 '14 at 22:22
  • Glad to hear. Please mark my answer as the answer then. => http://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. – Shiva Oct 31 '14 at 22:37
  • 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 – IFeelYourPain Oct 31 '14 at 23:34