Range inside Array not working

[RCL]

With macro recorder I generated this code

Sub Macro6()
ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _
Criteria1:=Array("1", "2"), Operator:=xlFilterValues
End Sub

My objetive is to replace the array values with my named range, so I
do this:

In cell A1 I write value 1
In cell A2 I write value 2
I create a named range called "CR", wich refers to range("A1:A2")

Then I modify my macro to this, to include the named range inside the
array:

Sub Macro6()
ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _
Criteria1:=Array(Range("CR")), Operator:=xlFilterValues
End Sub

The problem is that it doesn´t work...What I am doing wrong?

 

[JMay]

What happens if you remove the array()
and just do it as follows?:

Sub Macro6()
ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _
Criteria1:=Range("CR"), Operator:=xlFilterValues
End Sub

 

[Bob Phillips]

That only filters the 2's for me.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



With macro recorder I generated this code

Sub Macro6()
ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _
Criteria1:=Array("1", "2"), Operator:=xlFilterValues
End Sub

My objetive is to replace the array values with my named range, so I
do this:

In cell A1 I write value 1
In cell A2 I write value 2
I create a named range called "CR", wich refers to range("A1:A2")

Then I modify my macro to this, to include the named range inside the
array:

Sub Macro6()
ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _
Criteria1:=Array(Range("CR")), Operator:=xlFilterValues
End Sub

The problem is that it doesn´t work...What I am doing wrong?

 

[RCL]

Hi JMay, If I use:
Criteria1:=Range("CR")
my Excel crashes !

Doing more research, I have discovered that my array must be in the
same row , so I changed my range CR, which now refers to A1 and B1.
Doing this I don´t get the error, but the filter doesn´t show
anything...

So, I still need help on how to convert

Criteria1:=Array("1", "2")

TO

Criteria1:=Range("A1:B1") or Range("CR")

 

[JMay]

What is in your Range A1:B1 (Values?) << Also, What Sheet name is it
in?

What is the sheetname of your autofitered data?

 

[RCL]

- My Data Table is located in sheet2
- It has a first column with month numbers (1,2, 3, 4...)

In sheet 4 I have
- Value 1 in A1
- Value 2 in B1
- Named Range "CR" refers to Range("A1:B1")
(it is a public named range)

 

[JMay]

Change to Suit: (This worked for me) << on a smaller scale

Sub Macro7()
ActiveSheet.Range("$A$1:$E$10").AutoFilter Field:=1, _
Criteria1:=Range("CR")(1), Operator:=xlOr, _
Criteria2:=Range("CR")(2)
End Sub

Jim May