Linear regression and selecting a range of contiguous cells in a column

[drfish]

Hi, I'm a complete novice when it comes to VB so need some help.

I would like a macro that performs linear regression for different cell ranges each time it is run, depending on the number of contiguous cells in the columns.

I have managed to recorded a macro in Excel to perform a linear regression using the Data Analysis Toolpak for a fixed cell range:

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 15/09/2008
'

'
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$B$1:$B$3"), _
ActiveSheet.Range("$A$1:$A$3"), False, False, , ActiveSheet.Range("$A$9") _
, False, False, False, False, , False
End Sub

And i have found some code that selects an entire range of contiguous cells in a column:

Sub b()
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select
End Sub

So I thought that if i replaced ActiveSheet.Range("$B$1:$B$3") in the above with this code that hopefully it would work:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 15/09/2008
'

'
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("b1", ActiveSheet.Range("b1").End(xlDown)).Select, _
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select, False, False, , ActiveSheet.Range("$A$9") _
, False, False, False, False, , False
End Sub

... but unfortunately not and I get the excel error message saying: "Regression - Input Y range must be a contiguous reference", so I obviously need another approach!

I'd be very grateful if anyone can help point me in the right direction.

Thanks!