Search each cell in range for match in an array

[spidaman]

Can anyone help find a fast solution for finding an Instr match for each cell in a range from an array please?

The code below indicates what I'm trying to do, but I know it currently won't search against each value in myArray.

myArray = UKP.Worksheets("lookup").Range("A1:A30").Value

If frmTest.RmBox.Value = True Then
        For Each cel In Hyprange
              If InStr(cel.Value, myArray) > 0 Then
                cel.EntireRow.Delete
              End If
        Next
End If

How can I adjust the Instr formula to search against each value in the array?

Any advice on improvement for the look-up speed also very welcome.

Many thanks in advance.

 

[Fluff]

Am I correct in thinking that using Union method here creates a range of all the rows with matches to values in the array, allowing deletion altogether?

Yes you are
When you get the 1st match Rng is Nothing so you set Rng as the Cel, after which you can use Union.
If you try to do a Union when Rng is Nothing you will get an error.

 

[JLGWhiz]

The rng variable allows the Union parameters to expand on each iteration of the loop. You cannot set the rng to a specific range prior to running the loop because you don't know where the matched items reside.

 

[spidaman]

Thanks @Fluff and @JLGWhiz understood more clearly now

 

[mikerickson]

How about using

IF IsNumeric(Application.Match("*"&cell.Value&"*", myArray, 0)) Then

instead of looping through myArray with InStr.

 

[mikerickson]

The rng variable allows the Union parameters to expand on each iteration of the loop. You cannot set the rng to a specific range prior to running the loop because you don't know where the matched items reside.

One method might be to give Rng a value way outside of what the loop might add and then stripping that of at the end

Set rng = Range("AA9999")

For each cell in HypRange
    If something then Set rng = Application.Union(cell, rng)
Next cell

Set rng Application.Instersect(rng, Range("A:Z")