Is there a way to load an array with data from a worksheet? Here is
the code I have.

ActiveSheet.ListObjects("List12559").Range.AutoFil ter Field:=1,
Criteria1:= _
Array("103", "113", "123", "213", "220", "227", "231", "235",
"290", "500", _
"540", "900"), Operator:=xlFilterValues

These values are in A1-A12. I am hoping to make this more dynamic so
when I add a criteria I can just add it to the sheet and not to the
sheet and the VBA macro.

Any help woudl be appreciated,
Jay

Try the hereunder code.
The 2 Message Boxes prove that the 12 values are kept, each' in the
respective cell.
=====================
Sub Fill_One_Dimensional_Array()
Dim Rng As Range
Arr = Range("A1:A12")
MsgBox Arr(3, 1)
MsgBox Arr(5, 1)
End Sub
================
To make the list dynamically - check for the last filled cell in column "A":
LR = Cells(Rows.Count ,1).End(xlUP).Row
then use LR in the Array filling command:
Arr = Range("A1:A" & LR)
Micky


"jlclyde" wrote:

Is there a way to load an array with data from a worksheet? Here is
the code I have.

ActiveSheet.ListObjects("List12559").Range.AutoFil ter Field:=1,
Criteria1:= _
Array("103", "113", "123", "213", "220", "227", "231", "235",
"290", "500", _
"540", "900"), Operator:=xlFilterValues

These values are in A1-A12. I am hoping to make this more dynamic so
when I add a criteria I can just add it to the sheet and not to the
sheet and the VBA macro.

Any help woudl be appreciated,
Jay
.

I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
criterial:=myarr, operator:=xlfiltervalues



jlclyde wrote:

Is there a way to load an array with data from a worksheet? Here is
the code I have.

ActiveSheet.ListObjects("List12559").Range.AutoFil ter Field:=1,
Criteria1:= _
Array("103", "113", "123", "213", "220", "227", "231", "235",
"290", "500", _
"540", "900"), Operator:=xlFilterValues

These values are in A1-A12. I am hoping to make this more dynamic so
when I add a criteria I can just add it to the sheet and not to the
sheet and the VBA macro.

Any help woudl be appreciated,
Jay

--

Dave Peterson

On Dec 7, 12:01*pm, Dave Peterson wrote:
I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
* myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
* *criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. I get subscript out of range. Any other
thoughts? I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

On Dec 7, 12:51*pm, jlclyde wrote:
On Dec 7, 12:01*pm, Dave Peterson wrote: I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
* myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
* *criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. *I get subscript out of range. *Any other
thoughts? *I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Dave,
I was wrong.....Your code works fine when I am pointing at the right
list. That is why I was gettign the error.

Thanks for the help,
Jay

On Dec 7, 1:36*pm, jlclyde wrote:
On Dec 7, 12:51*pm, jlclyde wrote:





On Dec 7, 12:01*pm, Dave Peterson wrote: I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
* myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
* *criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. *I get subscript out of range. *Any other
thoughts? *I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Dave,
I was wrong.....Your code works fine when I am pointing at the right
list. *That is why I was gettign the error.

Thanks for the help,
Jay- Hide quoted text -

- Show quoted text -

Dave,
I was wrong again....Here is the code I ahve and it is sorting th
ecorrect list it just does not leave nay value on the screen. I am
not sure what is goign on here is my code.
Jay
Sub Macro3()
Dim MyArray As Variant
With Sheet4
MyArray = .Range("A5", .Cells(5, .Range("A5").End
(xlToRight).Column)).Value
End With

Sheet5.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:= _
MyArray, Operator:=xlFilterValues
End Sub

I think the problem is with this statement:

MyArray = .Range("A5", .Cells(5, .Range("A5").End(xlToRight).Column)).Value

This .cells(5,.range("A5").end(xltoright).column will result in a number. And
that isn't gonna be valid argument inside Range().

So maybe...

MyArray = .Range("A5:A" & .Cells(5, .Range("A5").End(xlToRight).Column)).Value
or
MyArray = .Range("A5", .Cells(5, .Range("A5").End(xlToRight))).Value

I like starting at the far right (or bottom) and working the way left (or up):

MyArray = .Range("A5", .Cells(5,.columns.count).end(xltoleft)).Value

But I thought your list was in A1:A12--not horizontal????

(Still untested--in either direction.)

jlclyde wrote:

On Dec 7, 1:36 pm, jlclyde wrote:
On Dec 7, 12:51 pm, jlclyde wrote:





On Dec 7, 12:01 pm, Dave Peterson wrote: I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. I get subscript out of range. Any other
thoughts? I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Dave,
I was wrong.....Your code works fine when I am pointing at the right
list. That is why I was gettign the error.

Thanks for the help,
Jay- Hide quoted text -

- Show quoted text -

Dave,
I was wrong again....Here is the code I ahve and it is sorting th
ecorrect list it just does not leave nay value on the screen. I am
not sure what is goign on here is my code.
Jay
Sub Macro3()
Dim MyArray As Variant
With Sheet4
MyArray = .Range("A5", .Cells(5, .Range("A5").End
(xlToRight).Column)).Value
End With

Sheet5.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:= _
MyArray, Operator:=xlFilterValues
End Sub

--

Dave Peterson

On Dec 7, 2:50*pm, Dave Peterson wrote:
I think the problem is with this statement:

*MyArray = .Range("A5", .Cells(5, .Range("A5").End(xlToRight).Column)).Value

This .cells(5,.range("A5").end(xltoright).column will result in a number. *And
that isn't gonna be valid argument inside Range().

So maybe...

*MyArray = .Range("A5:A" & .Cells(5, .Range("A5").End(xlToRight).Column)).Value
or
*MyArray = .Range("A5", .Cells(5, .Range("A5").End(xlToRight))).Value

I like starting at the far right (or bottom) and working the way left (or up):

*MyArray = .Range("A5", .Cells(5,.columns.count).end(xltoleft)).Value

But I thought your list was in A1:A12--not horizontal????

(Still untested--in either direction.)





jlclyde wrote:

On Dec 7, 1:36 pm, jlclyde wrote:
On Dec 7, 12:51 pm, jlclyde wrote:

On Dec 7, 12:01 pm, Dave Peterson wrote: I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
* myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
* *criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. *I get subscript out of range. *Any other
thoughts? *I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Dave,
I was wrong.....Your code works fine when I am pointing at the right
list. *That is why I was gettign the error.

Thanks for the help,
Jay- Hide quoted text -

- Show quoted text -

Dave,
I was wrong again....Here is the code I ahve and it is sorting th
ecorrect list it just does not leave nay value on the screen. *I am
not sure what is goign on here is my code.
Jay
Sub Macro3()
* * Dim MyArray As Variant
* * With Sheet4
* * * * MyArray = .Range("A5", .Cells(5, .Range("A5").End
(xlToRight).Column)).Value
* * End With

* * Sheet5.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:= _
* * * * MyArray, Operator:=xlFilterValues
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,
If you get a moment to test it does not show any of the values in the
array. I had the data originally going down, but then I changed then
to be column headings on a seperate list. Sorry for any confusion.

Thanks,
Jay

I started up xl2007.

I put some data into c7:J27.
I used Insert|Table to create the table (with headers). It defaulted to a name
of Table1.

I added some entries in C5:E5 that matched some of the values in the first
column of my table.

Then I ran this macro:

Option Explicit
Sub testme()

Dim myArr As Variant

With Worksheets("sheet1")

myArr = .Range("a5", .Cells(5, .Columns.Count).End(xlToLeft)).Value

.ListObjects("Table1").Range.AutoFilter Field:=1, _
Criteria1:=myArr, Operator:=xlFilterValues

End With

End Sub

If this doesn't help, please give more details--where the data is, where the
table is, and where that criteria is.






jlclyde wrote:

<<snipped
Dave,
If you get a moment to test it does not show any of the values in the
array. I had the data originally going down, but then I changed then
to be column headings on a seperate list. Sorry for any confusion.

Thanks,
Jay

--

Dave Peterson

Ps. Ron de Bruin has lots of vba samples for working with lists and tables
he
http://www.rondebruin.nl/tips.htm
look for Table

jlclyde wrote:
<<snipped
--

Dave Peterson