Way to retrieve values from a dynamic named range?

Hoping someone on here knows the answer, it's probably something simple I'm overlooking.

Currently we have a sheet with pre-defined values in columns such as:
A
1 MyValueA
2 MyValueB
3 MyValueC


Which I refer to as a named set:
MyNamedSet: =Lists!$A$2:$A$3

And pull into an array in my VB code:
MyArray = Evaluate(Names("MyNamedSet").RefersTo)

Thus yielding the following array:
MyArray (1,1) = MyValueA
MyArray (1,2) = MyValueB
MyArray (1,3) = MyValueC

What I would like to do with this named set is make it to where it allows the user to add additional rows to the Lists worksheet which are then pulled into the array at macro runtime.

To this end, I changed the Named item to be
MyNamedSet: =OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)

My hope was that this would automatically pull in the entire set of values from the "A" column, but instead it simple returns MyArray = "=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)".

Any ideas as to what I am doing wrong? I am trying to elimitate the need for the named range to be updated every time a new column value is added, am I going about things the correct way?

Might be easier for people to investigate if you post a sample workbook with data and code in context.

Regards

Thanks Pierre, what's odd is that if I add an additional value, only the new value is getting retrieved, not sure what's going on here.

What if you use:

Please Login or Register  to view this content.

It shouldn't do - note the RefersToRange, not RefersTo.

If you assign the value of a multiple cell range to a Variant, you always get a 2D array. If you don't want that, then Transpose it.

Please Login or Register  to view this content.

Glad to help.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save