Reference named range in AutoFilter criteria

[Celly]

I am trying to reference a named range in an AutoFilter like so:

Example 1

[Range1].AutoFilter field:=1, Criteria1:=">='" & ThisWorkbook.Name & "'![Range2]", VisibleDropDown:=False

No syntax seems to work. I can convert range2 to a string, and that is fine:

Example 2

[Range1].AutoFilter field:=1, Criteria1:=">=" & [Range2], VisibleDropDown:=False

It really isn't the same thing, it becomes static versus dynamic. I would prefer Example1 for performance and simplicity in refreshing the filter. In other words, this would theoretically refresh changes to Range1:

Sheet1.AutoFilter.ApplyFilter

In Example 2, the AutoFilter has to be regenerated.

 

[mart37]

I think that what you wanted is only possible with a worksheet_change event.

 

[Celly]

I think that what you wanted is only possible with a worksheet_change event.

Yeah I didn't believe this is possible, but I'm often surprised with Excel. Oh well.