Cut out rows based on wildcard criteria

[amitcohen]

Hi

This code loop through list of criteria and move(cut) any row with a match.

How can I use a 'wild card' here?

    Dim TheRange As Range
    Dim TheCell As Range
    Set TheRange = Range("A1:E3500", Range("A65536").End(xlUp))
    Dim R      As Long
    
    
'Start check list by criteria
For i = 1 To 5 Step 1

    For Each TheCell In TheRange.Cells
     If TheCell = Sheets("Criteria").Range("A" & i).Value Then
                    R = TheCell.Row
            Range(Cells(R, 1), Cells(R, 5)).Cut _
Destination:=Sheets("RemovedData").Range("A65536").End(xlUp).Offset(1, 0)
        End If
    Next TheCell

Next i

Thanks for the help,

Amit.

 

[VoG]

Maybe like this

If TheCell.Value Like "*" & Sheets("Criteria").Range("A" & i).Value & "*" Then

 

[amitcohen]

Thanks VoG
This is great!!

If I may ask one more Q here..
(Or should I start new thread)

How to re-run the macro so it will check the criteria again, this time on column B ?


Amit

 

[VoG]

Try

If TheCell.Value Like "*" & Sheets("Criteria").Range("A" & i).Value & "*" _
    Or TheCell.Value Like "*" & Sheets("Criteria").Range("B" & i).Value & "*" Then

 

[amitcohen]

VoG,
Thanks you so much for the help

Its perfect.

Amit