Global Array - How To Size

We have a sheet inside a workbook that contains product item codes and specifications for each of those items. These items are generally in one of four product groups or categories. The item codes are all in the first row with each of the five rows below containing one of the specs, the last of which is the product group.

The cell just above the first item code in each category is a named range. It is above the first item code so that as items are added they can be sorted without moving the named range that the code uses as it's starting point.
Example, cell B1 is the named range used in the code later.

Please Login or Register  to view this content.

At the present time we have 165 distinct item codes, but this can go up or down, generally up through our Fiscal Year and down as we get ready to start a new Fiscal Year.

I have created the following code to pull the data into a global array so it can be used quickly in a user form.

My first question is:
How is the best way to declare the global array? I arbitrarily picked 250 as that is about 10% more than the most item codes we have produced in a given Fiscal Year.

My second qustion is:
Is there a more efficient way of gathering this information? There is intentionally an empty column between each group of item codes going across the sheet.

Please Login or Register  to view this content.

This helps a lot!!