# VBA to create n arrays where each array is based on a subset of values in a named range

#### campione

##### New Member
Hi guys

I would greatly appreciate if someone could help me with the coding for the following:
- use a loop to create n arrays in VBA where each array is based on a descending number of values in a named range

So assuming my named range has 10 values,
- the first array is simply the original named range with 10 values
- the second arrray will exclude the 1st value in the named range, but include the remaining 9
- the third array will exclude the 1st TWO values in the named range, but include the remaining 8 and so on

I've been trying for hours but can't get it right?

I tried to base the coding on the resize formula, but it crashed and burned.
For i = n
Arr(i) = Range("Test").Resize(i, 1)
Next i

But I think that coding applied to element i in Array Arr as opposed to creating Arr i
Any ideas?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
why do you need 10 different arrays? if you have 1 array with all of the information within the range. you can reference any part of that array instead of creating 10 different arrays.

why do you need 10 different arrays? if you have 1 array with all of the information within the range. you can reference any part of that array instead of creating 10 different arrays.

Your idea might be much more efficient. If you pull a named range into an array, how do I reference the first 10 values in an array to use them in say a sumproduct? I know how to reference a specific element in an array but not a subset.

I need to be able to loop through and use subsets of the array in calculations: the first element, then the first two, the first 3, etc. I will use the subsets in UDFs and eventually output the answers via an array UDF formula

Your idea might be much more efficient. If you pull a named range into an array, how do I reference the first 10 values in an array to use them in say a sumproduct? I know how to reference a specific element in an array but not a subset.

I need to be able to loop through and use subsets of the array in calculations: the first element, then the first two, the first 3, etc. I will use the subsets in UDFs and eventually output the answers via an array UDF formula

I read on another forum from a similar post that:
"There is no way to pass a subset of an array to a function or a worksheet function just using VBA. VBA does not provide methods to manipulate subsets of arrays or to pull out subsets of arrays. You can build a subset of an array using index.

Personally, I would just loop through the array and build a new array that is the subset of the existing array."

Do you guys agree?
How can I loop through an array and build new arrays where first new array is the first 1st value, then second array is the first 2 values and so on?

What exactly are you trying to do? what do you have so far?

What exactly are you trying to do? what do you have so far?

Hi hateme28

I have a UDF which uses two ranges as inputs: a column containing returns and column containing weights.
Assume there are 10 values in each column, I want to loop through and do a series of sumproducts on different subsets of the ranges.

The first iteration of the loop will do a sumproduct of the top weight with the bottom return, the second iteration will so a sum product of the top 2 weights and the bottom 2 returns, and so on.
The last nth iteration of the loop will do a sumproduct of all the weights and returns.

I am stuck because I don't know how to reference a subset of an array so that I can use the first n values of the array/range in a UDF or worksheetfunction.

I've read that it's not possible in Excel VBA. Someone else said it's possible if you use the GetMethod.

Replies
1
Views
615
Replies
4
Views
242
Replies
7
Views
132
Replies
5
Views
131
Replies
6
Views
206

1,203,620
Messages
6,056,327
Members
444,861
Latest member
B4you_Andrea

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back