Storing excel data into several arrays in VBA?

Question

I have several sets of data in 1 excel sheet. I would like to store each of the sets of data into their own array so that I can do the necessary calculations after. I saw this thread on how to get data and store it into an array for one set of data, but how do I go through and do this data in Column A, Column B, C etc. I would like to use some kind of loop to go through the different columns, but since the columns are defined by letters and not numbers, I'm not sure how to increment them.

Also, if anyone knows some good resources on how to plot data from the vba code, that would also be appreciated. Everything I have seen is for older versions of excel (I'm using Excel 2016).

Thanks.

Here is the code mentioned from the link:

Dim Ar as Variant

Ar = Sheets("Sheet").Range("A1:A10000").Value

Please add the code part to your question ([edit]) that your question text is referring to, otherwise your question is very unclear what exactly you asking about. — Pᴇʜ, Feb 19 '18 at 13:55
I think you've answered your own question. Also, Column A = 1, B = 2, C = 3 and so on — Tom, Feb 19 '18 at 13:55
You could [get the letter of each column](https://stackoverflow.com/questions/13788279/how-can-i-get-the-column-letters-of-a-cell-need-to-make-it-work-past-the-z-colu) or you could use `Offset` — cybernetic.nomad, Feb 19 '18 at 13:56
@Tom - Could you please expand on how though? How would I update the range with a counter? Like instead of A1:A10000 if I wanted 'A' to be a counter and increase by 1 each time. How does it work? — Anonymous, Feb 19 '18 at 14:02
Did you think about using `Cells(RowIndex, ColumnIndex)` this can also be used to define a range like `Range(Cells(r, c), Cells(r, c))` Both Row and ColumnIndex are numbers, so you can easily loop through. Or if you want to access a whole column use e.g. `.Columns(2)` for column B. — Pᴇʜ, Feb 19 '18 at 14:02

Pᴇʜ · Accepted Answer · 2018-02-19T14:31:06.607

If you want to loop through entire columns

Dim c As Long
For c = 1 To 10
    Debug.Print Worksheets("Sheet1").Columns(c).Address
Next c

Or through columns but e.g. only row 5 to 15

Dim c As Long
For c = 1 To 10
    Debug.Print Worksheets("Sheet1").Range(Cells(5, c), Cells(15, c)).Address
Next c

But according to the link you gave in your question you can also read a complete range into a 2 dimensional array, which should be even faster than reading column by column.

Dim DirArray As Variant
DirArray = Worksheets("Sheet1").Range("A1:C5").Value 'for a range

or even more than one entire column at once like

DirArray = Worksheets("Sheet1").Range(Columns(1), Columns(3)).Value 'for column 1 to 3

and access it like

Debug.Print DirArray(2, 3) 'where 2 = row 2 and 3 = column C

note that counting in arrays may be different if your first column and row is not 1. For example if you use

DirArray = Range("B2:C10").Value
Debug.Print DirArray(1, 1) 'this is the value of Range("B2")

the counting gets shifted by the offset of the first row and column.

Thank you! This really helps with understanding how to do this. Just a quick follow up questions, how would I ignore the first row (if it has headers)? — Anonymous, Feb 19 '18 at 15:52
@Anonymous This questions cannot be answered in a comment here. And it cannot be answered until you refer it to a specific line of code. You will need to ask a new question, add the code there that you have tried and ask specifically to that piece of code then. — Pᴇʜ, Feb 19 '18 at 15:59

Storing excel data into several arrays in VBA?

1 Answers1