Filter Data Using List From Another Sheet

[justme101]

Hey guys,

So the title is self-explanatory, i have data in one sheet where i want to put a filter in column F of the active sheet (named "RawData") using the list of filter criteria in Column A of a DIFFERENT sheet (Named "Rules").

I got the following code from the net after a lot of searching and modifications:

Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("RawData")
Set wsL = Worksheets("Rules")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("FunctionList")


vCrit = rngCrit.Value


rngOrders.AutoFilter _
Field:=6, _
Criteria1:=Application.Transpose(vCrit), _
Operator:=xlFilterValues

I already named the list of criteria as "FunctionList" and it is working perfectly.

The PROBLEM i am facing is that this code works for a column where data is only in LETTERS (for e.g. ABC, XYZ etc) . I tried the same code on a column which has numbers in the cells and the filter is activated but it does not show me any data or in other words, hides ALL the rows containing the data.

A sample data set is given below:

FilterCriteria	1	2
23123	A	F
23454	FA	FASD
4656	EH	FD
657	A	HK
9999	AHB	FA
9999	AFD	AD
443412	ASA	VHHY
9999	YDG	TYD
22	SD	DFGS

<tbody>
</tbody>

Now, in another sheet there is this column where i have the list through which i want to filter:

FilterList

9999

657

120

<tbody>
</tbody>

In this case, the macro should filter out the data with these 3 criteria and if there is no criteria match or only some data match,then filter only with those data then move on with the next step (which i will write later on after this macro is made). As i said, this list will change on a weekly basis but for the life of me i cannot see what is wrong with this. Please let me know if you need clarifications. Thank you.

 

[Marcelo Branco]

The array criteria must be an array of strings, not numbers.
To convert the array of numbers to an array of strings try

Sub aTest()
    Dim vCrit As Variant
    Dim wsO As Worksheet
    Dim wsL As Worksheet
    Dim rngCrit As Range
    Dim rngOrders As Range
    
    Set wsO = Worksheets("RawData")
    Set wsL = Worksheets("Rules")
    
    Set rngOrders = wsO.Range("$A$1").CurrentRegion
    Set rngCrit = wsL.Range("FunctionList")
    
    [COLOR=#ff0000]vCrit = Split(Join(Application.Transpose(rngCrit.Value)))[/COLOR]
    
    rngOrders.AutoFilter _
    Field:=6, _
    Criteria1:=[COLOR=#ff0000]vCrit[/COLOR], _
    Operator:=xlFilterValues
End Sub

Hope this helps

M.