Dim aArray As Variant
aArray = Application.WorksheetFunction.Transpose _
(Range("E4:E" & Range("E65536").End(xlUp).Row))

To test:

Dim aArray As Variant
Dim i As Long
aArray = Application.WorksheetFunction.Transpose _
(Range("E4:E" & Range("E65536").End(xlUp).Row))
For i = LBound(aArray) To UBound(aArray)
MsgBox i & " " & aArray(i)
Next 'i


Regards

Trevor


wrote in message
oups.com...
Hi,

I am having trouble reading values from excel into an array. I have
data that begins in cell E4 and will have varying length based on how
much data the user lists. How can I read that data into an array.
This is what I have tried so far:

Dim k as integer

ActiveCell.Range("E4").Select
While activecell.value < ""
yArray(k) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
k=k+1
Wend

What am I doing wrong or what should I be doing? Any help is much
appreciated.