Assigning an Array Using Index/Named Ranges

[bookiiemonster]

Hi!

I have a workbook where I need to refer to specific columns, so I've named them, and then I need to build arrays that need to be dynamic (as in, the amount of values in the columns will change). In all scenarios, each array will just be one column.

Dim numOfRows As Integer
numOfRows = Application.WorksheetFunction.CountA(Range("MyNamedRange"))
Dim MyNamedRangeArray() As Variant
MyNamedRangeArray = Range("Index(MyNamedRange,1):Index(MyNamedRange," & numOfRows & ")")

The MyNamedRange is Column A in this sheet. Attaching a screenshot of the range and the name set up.

If I do

Range("Index(MyNamedRange,1):Index(MyNamedRange," & numOfRows & ")").Select

It highlights the correct cells, so I know this Range is written correctly (and I use Range with Index for Named Ranges all the time).

No matter what I do (like add .Value or Value2 to the end of the Range, use Transpose, ect) I can't get MyNamedRangeArray to fill with the values of that Range. I've ran code that prints out the values of an Array or counted the number of values in an Array, and if I blunt force fill the Array, then those codes spit out the values. I really need this to be dynamic because more values will get added or removed from this column (there will always be quite a few, I want to say never less than 10, my example is just using a couple for testing purposes). If there's another way to easily make an Array dynamic in size, happy to rethink my strategy.

Any and all advice is greatly appreciated, as this is my first time using Arrays in VBA (I'm relatively new to VBA coding in general). Hoping I'm just missing something basic.

Thank you!!

 

[Artik]

I don't like the Locals window because there is too much “garbage” in it. In the Watches window I only have what I need.
But these are my personal feelings.

Artik

 

[bookiiemonster]

I don't like the Locals window because there is too much “garbage” in it. In the Watches window I only have what I need.
But these are my personal feelings.

Artik

Thanks for the tip!!