Change a static range by a dynamic one

[SiO2]

Hi,



I found on internet the following code to calculate the coefficients of a polynominal regression line. This is a static range with 10 elements.
How can I make the range dynamic (for more or less elements).
I try to replace [a1:a10] by a dimensioned range, and [b1:b10] by another range, but this results in an error.
Does anyone knows an answer. Thanks in advance.
SiO2

Sub Test()
 Dim Y 
 Dim X
 Dim Arr1
 Dim Arr2
 With Application
  Y = .Transpose([a1:a10])
 X = .Transpose([b1:b10])
 Arr1 = .Power(.Transpose(X), Array(1, 2, 3))
 Arr2 = .LinEst(Y, .Transpose(Arr1))
End With
 MsgBox "coefficients are " & Chr(10) & Join(Arr2, Chr(10))
 End Sub

[NoSparks]

Depending on how the data is loaded into columns A&B, I'd consider using an Excel table, then you could just refer directly to the table columns.

A page on table stuff... https://www.thespreadsheetguru.com/b...t-excel-tables

[NoSparks]

OR

Sub Test()
 Dim Y
 Dim X
 Dim Arr1
 Dim Arr2
 
 Dim lr As Long
 
 lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 
 With Application
     'Y = .Transpose([a1:a10])
     'X = .Transpose([b1:b10])
     
     Y = .Transpose(Range("A1:A" & lr).Value)
     X = .Transpose(Range("B1:B" & lr).Value)
     Arr1 = .Power(.Transpose(X), Array(1, 2, 3))
     Arr2 = .LinEst(Y, .Transpose(Arr1))

End With

MsgBox "coefficients are " & Chr(10) & Join(Arr2, Chr(10))

End Sub

[SiO2]

Hello Nosparks.

The second option is working.
Fantastic. Thanks a lot.

SiO2