AutoFilter with dynamic range criteria

[AlexCS]

Hi everyone,

Can anyone tell me if it is even possible to use a dynamic range as an autofilter criteria? I tried with the below code and it does not work..a close variation also gets my Excel to crash

Dim var As Integer
Sheets("Master").Select
ActiveSheet.Cells(1, 1).CurrentRegion.Select
var = Selection.Rows.Count

Dim sir As Range
Set sir = Sheets("Master").Range(Cells(2, 1), Cells(var - 1, 1))
Sheets("Total Despatched").Select
ActiveSheet.Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$DI$2500").AutoFilter Field:=94, CriteriaRange:=sir, Operator:=xlFilterValues

What I am trying to do is identify the range in the Master tab and then using it to filter the Total Despatched tab for each entry in the range.

Any help would be much appreciated!

Thanks,

Alex

 

[Firefly2012]

The filtervalues need to be passed as a 1 dimensional array, so assuming it is a verticla single column range you could use:

ActiveSheet.Range("$A$1:$DI$2500").AutoFilter Field:=94, CriteriaRange:=Application.Transpose(sir), Operator:=xlFilterValues

 

[AlexCS]

Good morning Firefly,

Thank you very much for your suggestion. Couldn't wait to get to work and try this but unfortunately, when trying to run the modified macro, I got Run time error 1004 - Application Defined or Object Defined Error.

I also tried to define another range to take the value of Application.Transpose(sir) and filter by it but I got Run time error 424 Object Required.

I will look at the exact syntax for using worksheet applications in VBA code but other than this I am out of ideas..is there anything else you would try?

Best,

Alex

 

[Firefly2012]

My apologies - you have an invalid argument in the Autofilter. It should be:

ActiveSheet.Range("$A$1:$DI$2500").AutoFilter Field:=94, Criteria1:=Application.Transpose(sir), Operator:=xlFilterValues

 

[AlexCS]

Hi Firefly,

Sorry, tried to check my posts but there were some issues with the forum. I just tested your solution and it works perfectly! I am really happy to have learned something that can prove to be very useful in the future

Thanks a lot for your time!

Alex