Set values in an Array
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
|