Below is an example to copy values into an array in Excel.
' This code was borrowed from http://www.viiweb.com and is free to use without
' warranties or guarantees.
' pass in an array to fill
Sub fillArray(ByRef strArray() As String, ByRef intRowStart As Integer)
Dim intCurrentRow As Integer
Range("A" & intRowStart).Select ' assume the data is in column A
While ActiveCell.Value <> ""
ReDim Preserve strArray(intCurrentRow)
strArray(intCurrentRow) = ActiveCell.Value
intCurrentRow = intCurrentRow + 1
Tested Platform: Excel 2010