Get Values To Store In An Array VBA

Question

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.

drec4s · Accepted Answer · 2019-04-06 14:03:48Z

1

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

answered Apr 6, 2019 at 14:03

drec4s

8,1178 gold badges35 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BCLtd Over a year ago

This worked perfectly, thanks - I wasnt sure how to do this

Collectives™ on Stack Overflow

Get Values To Store In An Array VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related