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" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> mthomas
> ------------------------------------------------------------------------
> mthomas's Profile:
http://www.excelforum.com/member.php...o&userid=25649
> View this thread: http://www.excelforum.com/showthread...hreadid=392734
>

[mthomas]

Very close and with a little tweeking it works fine. Actually, I needed to delete rows that were <> the conditions. So, I just added the "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 i