Delete row if contains City, State, or Zip

[IFeelYourPain]

I don't really know what I am doing here, but I need 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 B, but I couldn't get this to work, and actually I now need it for column D.

Here is the code I have thus far:

Sub EOD()
'
' ZIP Macro
'
' Keyboard Shortcut: Ctrl+e
'
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:X").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    '37546
    Sheets("37546").Select
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:X").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    '39201
    Sheets("39201").Select
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:X").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    '36241
    Sheets("36241").Select
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:X").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    'Delete row containing City State and Zip
    Dim r As Integer
      Dim cityStateZip

      cityStateZip = InputBox("Input the city, state, or zip code from " & vbNewLine & _
        "column C for which you want rows removed.")
    
      For r = 1 To GetLastRow("37546", "B")
        If Left(CStr(Cells(r, 6).Value), 1) = "0" Or (Len(Orlando, Fl, 32803) > 0 And InStr(1, Cells(r, 3).Value, cityStateZip) > 0) Then
            Rows(r).EntireRow.Delete
            r = r - 1
        End If
      Next
End Sub

[alansidman]

Your delete portion of the code should run from the bottom to the top of the list.

So replace this:

 For r = 1 To GetLastRow("37546", "B")

With this:

For r = GetLastRow("37546", "B") to 1 Step -1

I don't think you need the line

r=r - 1

[stnkynts]

I have a feeling that your post doesn't accurately reflect what you really need. Here is what you asked for.

Sub RunMe()
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 = "city", "zip", "state"
                Rows(i).Delete
        End Select
    End If
Next i

End Sub

[IFeelYourPain]

I have a feeling that your post doesn't accurately reflect what you really need. Here is what you asked for.

Sub RunMe()
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 = "city", "zip", "state"
                Rows(i).Delete
        End Select
    End If
Next i

End Sub

That's exactly what I need, but it didn't work. I run the macro, and it just sits there.