Loop through rows using a wildcard.

[mthomas]

Hey all, as you can see I'm using multiple ifs here to quality deleting
a row. However, the case and spelling may be inconsistent. I would
prefer to use a wildcard to capture the values for "cashiering" and not
be limited to upper case. How would I go about this? Thanks in advance
for the expertise!

'***********Begin Code****************
Dim i As Long, beginRow As Long, lastRow As Long
myNum = Application.InputBox( _
prompt:="Please select the beginning row")

beginRow = myNum
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = lastRow To beginRow Step -1
If Cells(i, 1).Value <> "BANKRUPTCY" Then
If Cells(i, 1).Value <> "CASHIERING/BANK RESEARCH" Then
If Cells(i, 1).Value <> "CASHIERING" Then
If Cells(i, 1).Value <> "CASHIERING/WESTERN UNION" Then
If Cells(i, 1).Value <> "CASHIERING-SPEEDPAY" Then
If Cells(i, 1).Value <> "CASHIERING/NORTHBROOK" Then
If Cells(i, 1).Value <> "CASHIERING/WIRES" Then
If Cells(i, 1).Value <> "CASHIERING/Acq. WIRES" Then
Rows(i).Delete
End If
End If
End If
End If
End If
End If
End If
End If
Next i

'**************End Code************

Regards!

 

[Bob Phillips]

I think that this is what you are after

For i = lastRow To beginRow Step -1
If LCase(Cells(i, 1).Value) Like "*bankruptcy*" Or _
LCase(Cells(i, 1).Value) Like "*cashiering*" Then
Rows(i).Delete
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[mthomas]

Very close and with a little tweeking it works fine. Actually, I neede
to delete rows that were <> the conditions. So, I just added th
"ELSE". Thanks again for your help and God bless!

For i = lastRow To beginRow Step -1
If LCase(Cells(i, 1).Value) Like "*bankruptcy*" Or _
LCase(Cells(i, 1).Value) Like "*cashiering*" Then
'Do Nothing
Else
Rows(i).Delete
End If
Next