Filter List Using VBA Loop on a Named Range

Question

Could someone please help me understand how to create a filter on a table in Excel and select MULTIPLE criteria from a Named Range using VBA? Below is a simple example. Thanks so much for your help!

People      Fruit                   Filter List (Named Range)

Michael     Apples                Michael

Brad          Oranges              Brad

John          Grapes       

Michael     Oranges

Answer 1

You can use this code:

Range("A1:B5").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _

        ("Sheet1!Filter_List"), Unique:=False

Answer 2

Hi,

data in active sheet, in columns A-B,

Criteria (Define Name =myList) , in range E1:E2

pic1

result

pic2

vba

Sub AutoF_DefineName_Criteria()

Const sName As String = "myList" '<< define name

On Error Resume Next

Dim ws As Worksheet

Set ws = ActiveSheet

ws.AutoFilterMode = False

Dim rng As Range, rr As Range

Set rng = ws.Range("A1").CurrentRegion

Dim n As Integer, t As Integer

n = Range(sName).Rows.Count

Dim v As Variant

ReDim v(1 To n)

t = 1

For Each rr In Range(sName)

v(t) = rr.Value

t = t + 1

Next

rng.AutoFilter Field:=1, Criteria1:=v, Operator:=xlFilterValues

End Sub

Answer 3

I just added the example as text. Thanks!

Answer 4

Sorry. The image didn’t show. Can you post it again?