AutoFilter - Criteria - Array - Range - Not working please assist.

[kd360]

Hi,
I wold like to use the autofilter to filter data based on the criteria that matches a given range in another sheet.

Following is my code
Sub Macro1()
'
' Macro1 Macro
Dim arr() As Variant

arr = Array(Sheet1.Range("B2"), Sheet1.Range("B3"))
'Using this the code works fine and the filter and array populate

'arr = Array(Sheet1.Range("B2:B18"))
'When this code is used it does not work and I really dont want to type references for all the cells.
'I've also tried 'Sheet1.Range("B2:B18").Value = Array(Sheet1.Range("B2:B18")) but not working

Sheet4.Select
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("C3").Select
Sheet4.Range("$A$3:$AK$11819").AutoFilter Field:=3, Criteria1:=arr, Operator:=xlFilterValues
Range("C3").Select
End Sub

Tried various options of creating Arrays, Ranges etc. from google but have not worked.
Please Assist!

The Final out put of the code should work as this:
Example Sheet 1 B2:B7 contain the vowels {'a', 'e', 'i', 'o', 'u'} and the sheet on which I want to apply autofilter should match the criteria of the range B2:B7 of Sheet1. I wish to apply the same with a criteria range of 50 items hence require it to work with reference of a range. Advanced filter has worked but it has to be done with auto filter.

Thanks

 

[Worf]

Hi
See if this example is useful:

Sub Filter2()


Dim sh As Worksheet, Arr()
Sheets("Sheet1").Activate
Arr = Application.Transpose(Range("b2:b5")) ' if it is a column, transpose it!
Set sh = Sheets("Sheet4")
sh.Columns("a:C").AutoFilter
sh.Range("a3:c30").AutoFilter 3, Arr, xlFilterValues


End Sub

 

[kd360]

Yes that worked! thank you very much for that! Worked perfect! :D Now how do I mark this as solved?!

 

[Worf]

1) You are welcome!

2) At this forum, I don't think we can formally mark a thread as solved. It would be nice if the title could change to

[SOLVED] AutoFilter - Criteria - Array...

Including the one at the e-mails sent when the thread changes.