Hello:

I have a routine that asks the user to select two ranges (using
application.input box). One of the ranges is a column vector rngY and
the second is a matrix or column vector rngX. The two regions are
required to have the same number of rows and the matrix must be
contiguous.

These two are passed to linest() function in Excel as:

Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)

Everything works fine and I get the expected results.

Now I want to change the result so that only the first n rows are
used. Something like:

Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5),
rngX.rows(1 to 5), True, True)

where the actual number of rows in rngY and rngX might be 10. ie I
want to use the first half the data to run the regression.


I know that for columns I can use:

Result = Application.WorksheetFunction.LinEst(rngY.Columns( 1),
rngX.Columns(2), True, True)

Which will correlate the Y variable with 2nd X variable. I would also
like to be able to the same thing for multiple columns which is also I
problem I have not solved

I could something like (which would allow me to select any subset of
rows):

Startrow = 1
Endrow = 5
For i = 1 To col
For j = Startrow To endrow
Xstore(j, i) = rngX(j, i)
Next j
Next i
For j = Startrow To endrow
Ystore(j) - rngY(J)
next j

Then Xstore and Ystore would contain the right data but this creates
two problems, it is slow and the linest function expect ranges.

Can someone suggest a strategy? Thank you.