Variable Range in VBA Regression

[EnergyModeler]

Hello,

I am trying to automate a linear regression but want it to work for variable sized data sets. This is what I have for a given set. The Y data always starts at X5 and the X data always starts at Y5, but the length of the column changes. Any ideas as to how I would allow for the regression to fit the data that is input (i.e. have the "$X$106 and $AG$106 change depending on data)?? Thanks for the help!

Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$X$5:$X$106"), _
ActiveSheet.Range("$Y$5:$AG$106"), False, False, , "", False, False, _
False, False, , False

 

[DanteAmor]

Try this

Sub test()
  Dim x As Long, y As Long
  x = Range("X" & Rows.Count).End(xlUp).Row
  y = Range("Y" & Rows.Count).End(xlUp).Row
  Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$X$5:$X$" & x), _
    ActiveSheet.Range("$Y$5:$AG$" & y), False, False, , "", False, False, _
    False, False, , False
End Sub

 

[EnergyModeler]

Thank you for your reply. I tried your suggestion but it returns an error "Regression - LINEST() function returns error. Please check input ranges again."

 

[EnergyModeler]

I figured it out!! It was taking into account empty cells below that had a hidden error message. Your code worked!! Thank you very much. It is appreciated!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.