Search Arrary for multiple values

[tanix]

Hi All,
Im writing a micro to do a search for me. I have an arrary of values on sheet1 (A9 to DB324) and a second array of values on sheet2 (A3 to A19). Im looking for a way search the arrary on sheet1 (the values will be in column E) for the values on Sheet2 and hides any row that does not contrain any of the searched values (ie sheet2 values)

Thanks
TN

 

[trunten]

May I suggest a helper column?

The following code will do what I am suggesting for you. If you comment out/delete the line I've indicated it'll keep the formula in the column as opposed to the value.

Sub a()
    With Sheets("Sheet1")
        With .Range("DC9:DC324")
            .Formula = "=COUNTIF(Sheet2!R3C1:R19C1,R[0]C5)"
            .Value = .Value 'You could comment this out.
            .EntireColumn.Hidden = True
        End With
        .Range("A8:DC324").AutoFilter 107, ">0", xlFilterValues
    End With
End Sub

 

[tanix]

Thanks however I forgot to add the the values in colum e contains 5 numbers some version of MR in front (MR##### or mr##### or MR-##### or M/R ##### or MR ##### etc) and the values in sheet2 only contain the 5 numbers ##### (no version of MR). Sorry about that. Again thank you I have been banging my head for days.

 

[trunten]

Thanks however I forgot to add the the values in colum e contains 5 numbers some version of MR in front (MR##### or mr##### or MR-##### or M/R ##### or MR ##### etc) and the values in sheet2 only contain the 5 numbers ##### (no version of MR). Sorry about that. Again thank you I have been banging my head for days.

change:
=COUNTIF(Sheet2!R3C1:R19C1,R[0]C5)

to:
=COUNTIF(Sheet2!R3C1:R19C1,RIGHT(R[0]C5,5))

 

[tanix]

Thanks again works great. One last question (I hope) about this part of the code.

With .Range("DC9:DC324")

This is the first time I have used a With statement and a little confussed about that it does. Lets say I have given the range ("DC9:DC324") a name of Sheet1Range would the following be the correct way to code it?

With .Range(Sheet1Range)