using VB to read in a column of values of varying length into an array

[moreilly5]

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.

 

[Bob Phillips]

yArray = Range(Range("E4"),Cells(Rows.Count,"E").End(xlUp))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Trevor Shuttleworth]

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