MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Values of a Range in an Array

  Asked By: Alex    Date: Feb 22    Category: MS Office    Views: 1075

Am able to capture a range using the below sub,but am not sure how i
can put these values into an array.

Sub test()
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
lastrowwithdata = ExcelLastCell.Row
Row = ExcelLastCell.Row
Do While Application.CountA(ActiveSheet.Rows(Row)) = 0 And Row <> 1
Row = Row - 1
last = Row
x = "a2"
x1 = "a" & last
x2 = x & ":" & x1
msgbox Range(x2)
End Sub

Now range x2 contains the starting cell as a2 and the last row
containing data in column A(say A5000).So i want to put the values of
A1:A5000 into an array.Any help on this woul be greatly appreciated.
Please reply as it is very urgent.



1 Answer Found

Answer #1    Answered By: Ricardo Smith     Answered On: Feb 22

> So i want to put  the values  of A1:A5000 into an array

Try this...

Dim myARRAY as Variant
myARRAY = MyXLSheet.Range("A1:A5000")

...where MyXLSheet was already defined (let me know if you need help
with this part). Hope this helps.

Didn't find what you were looking for? Find more on Values of a Range in an Array Or get search suggestion and latest updates.