CODEWITHSUNDEEP
excelvba
BCLtd
BCLtd

Reputation: 1461

Get Values To Store In An Array VBA

I have values stored in a sheet called config, and goes from H2 (the list is dynamic as more can be added) So I am using the following code:

roomCount = ActiveWorkbook.Worksheets("config").Range("H2", Worksheets("config").Range("H2").End(xlDown)).Rows.Count

So, this gives me the number of rows.

What I am trying to do, but I just can't see to get my head around is, do a loop to store these values from H2 onwards (using my code above) into an array.

and maybe for argument's sake msgbox the array upon a button click.

Upvotes: 0

Views: 779

Answers (1)

drec4s
drec4s

Reputation: 8077

There is no need to loop through the cells to build an array. You can store the values directly in an Array like this:

Dim myvar as Variant    

myvar = ActiveWorkbook.Worksheets("config").Range("H2", Worksheets("config").Range("H2").End(xlDown))

And you can loop through all the elements using:

For Each ele In myvar
    Debug.Print ele
Next

Or directly access each individual element:

Debug.Print myvar(1,1) 'first element of the array
Debug.Print myvar(2,1) 'second element of the array

Upvotes: 1

Related Questions