VBA reference to named range

Question

I have following code in which I have tried to applied 3 filters, now what I want is, to apply this filter criteria based on named range I have stored relevant value in specific cell and given names to that range

for first criteria i.e. >=200 named range is VOLUME

for second criteria i.e. >=0.07 named range is MOVE

for third criteria i.e. >=400 named range is STRENTH

So what are the changes that I need to make in following code?

Sub FILTER2()

'FILTER CRITERIA
ActiveSheet.Range("$A$2:$AC$500").AutoFilter Field:=6, Criteria1:=">=200", _
    Operator:=xlAnd
ActiveSheet.Range("$A$2:$AC$500").AutoFilter Field:=21, Criteria1:=">=0.07" _
    , Operator:=xlAnd
ActiveSheet.Range("$A$2:$AC$500").AutoFilter Field:=28, Criteria1:=">=400" _
    , Operator:=xlAnd

Range("A1").Select
End Sub

Can you clarify which value is in your named range? Is it 200 or >=200? If the first, your criteria should be ">=" & Range("Volume").value. If the second it should just be Range("Volume").value — Plutian
– Plutian, Commented Nov 27, 2019 at 8:06
You might want to refrain from using ActiveSheet. Since you will also want to refer to a specific sheet holding your named ranges, a With statement seems at place here. — JvdV
– JvdV, Commented Nov 27, 2019 at 8:09

JvdV · Accepted Answer · 2019-11-27 08:19:14Z

1

As per my comment, you might want to consider a With statement, better not to refer to ActiveSheet

Sub FILTER2()

With Sheet1 'Change according to your sheets CodeName
    .Range("$A$2:$AC$500").AutoFilter 6, ">=" & .Range("VOLUME")
    .Range("$A$2:$AC$500").AutoFilter 21, ">=" & .Range("MOVE")
    .Range("$A$2:$AC$500").AutoFilter 28, ">=" & .Range("STRENTH")
End With

End Sub

answered Nov 27, 2019 at 8:19

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arshit patel Over a year ago

yes, it helped me a lot, thq for giving your valuable time to share ans.

Sergiu · Accepted Answer · 2019-11-27 08:42:34Z

0

you can try defining a variable to contain the value of your named range. Named Range in your sheet as "Volume" then i can store that value in var namedRange1.

example below:

Sub FILTER2()  

namedRange1 = range("Volume")

'FILTER CRITERIA
ActiveSheet.range("$A$2:$AC$500").AutoFilter Field:=6, Criteria1:=">=" & namedRange1, _
    Operator:=xlAnd

....same with the other lines, just define new variable to contain new named range

edited Nov 27, 2019 at 8:42

answered Nov 27, 2019 at 8:36

Sergiu

435 bronze badges

Collectives™ on Stack Overflow

VBA reference to named range

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related