Select a Named Range within the AutoFilter code

[waynewalker]

I am selecting a worksheet and then turning on the AutoFilter. I am then filtering the column on the specific number highlighted in red below.

What I would like to do is replace that specific number with a named range. Is that possible?

Sheets("MSO").Select
  Range("E6").Select
  ActiveSheet.AutoFilterMode = False
  Selection.AutoFilter
  ActiveSheet.ListObjects("MSO").Range.AutoFilter Field:=6, Criteria1:= _
     [COLOR=#ff0000]"5303030"[/COLOR]
  Range("MSO[[Column2]:[Column2]]").Select
  Selection.Copy

__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Chihiro]

Yes. You'd need to transpose named range (single column) and specify Operator.

So if named range is named "lstcrit1"... something like below.

Sub test()

With Sheets("MSO")
    If .AutoFilterMode Then .ShowAllData
    .ListObjects("MSO").Range.AutoFilter Field:=6, _
        Criteria1:=Application.Transpose(Range("lstcrit1")), _
        Operator:=xlFilterValues
End With
End Sub

 

[waynewalker]

Thanks, I will give this a try and see how it goes. I certainly do appreciate it.

 

[waynewalker]

I was able to get the macro to work - thanks so much for your help.