I need a 2-dimensinal dynamic array, but the problem is, that I don't have the 1st dimension as fixed number. It is read from Named Range instead. It doesnt change after the array is declared for the scope of procedure, but it may be initialized at start, when according cell is empty.

I have a sample of data, where an indetermined number of articles are delivered at certain dates (weekly or monthly). The maximal possible number of different weeks is stored in worksheet cell, which is defined as Named Range, p.e. MaxWeeks. I need to read those data into array p.e. arrData(1 To MaxWeeks+2, 1 To n), where arrData(1, n) is quantity of article n for 1st week, ..., arrData(MaxWeeks, n) is quantity of article n for last week, arrData(MaxWeeks+1, n) is article number, and arrData(MaxWeeks+2, n) is total quantity of article n.

The idea is to declare the dynamic array, and the use Redim Preserve to add new row for every new article. But I haven't found a way to make VBA to accept MaxWeeks as a constant (and because I may need to initialize MaxWeeks, I can't declare it in declarations section anyway), and I can't use Redim to add a new dimension.

Is there some solution?
Thanks in advance!

Arvi Laanemets