Read worksheet into 2 dimentional array

Question

As none of my searches resulted in a fairly understandable resolution, I am hereby posting my question on the hope of some assistance; since I am fairly new to VBA.

I am trying, as a first step, to read data from a worksheet into a two dimensional array in order to copy different parts of this table into other worksheets (with some manipulation).

The current worksheet contains some headers (which are irrelevant) and data that starts from cell (A4). I would probably have a set column value (let's assume it is 50 for now), but the number of rows is variable.

Another point, I don't know if it is important, is that some of the cells in any row may be empty, but the first cell will always have a value.

Any assistance or pointers will be greatly appreciated.

There is plenty of code available online (and on Stack Overflow for that matter). What is your question? — enderland, May 21 '14 at 16:46
Second result of searching your question title is - http://www.cpearson.com/excel/ArraysAndRanges.aspx — enderland, May 21 '14 at 16:47
enderland, thanks for the pointer, but already gone through it and it explains how to read based on a preset range (i.e. "A1:A10"), but my range (at least the rows) is variable. — elrayyes, May 21 '14 at 16:57
Search the interwebs for ways to find the last used row in Excel, and incorporate that into the code on Pearson's site. Also, please post the [code you tried](http://stackoverflow.com/help/on-topic) and what the unexpected results/errors were. — ARich, May 21 '14 at 17:01
Bookmark this link, it has everything you need to know about finding the last row (by VBA OG @SiddharthRout) http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Dan Wagner, May 21 '14 at 17:05

score 0 · Accepted Answer · answered May 21 '14 at 18:09

Here is a typical example of pulling 50 columns of data with a variable number of rows from the active worksheet into a two dimensional array:

Sub GetData()
    Dim r As Range
    Dim N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    arr = Range("A4:AX" & N)
End Sub

Read worksheet into 2 dimentional array

1 Answers1