Dynamic Regressions.

[JamisonHowell]

SO the issue I am having is that I need to create a macro that copies information into a new sheet and then runs a regression on that information. Sounds simple enough, right? So I recorded this Macro:
Sub h()
'
' h Macro
' h
'
' Keyboard Shortcut: Ctrl+h
'
Range(Selection,Selection.End(xlDown)).Select
Range(Selection,Selection.End(xlToLeft)).Select
Range(Selection,Selection.End(xlToLeft)).Select
Selection.Copy
Sheets.AddAfter:=ActiveSheet
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _
:=False,Transpose:=False
Range("B2").Select
Application.Run"ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$AA$2:$AA$430")_
, ActiveSheet.Range("$I$2:$K$430"),False, True, , "", False, False, _
False, False,, False
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "REG-HS"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "HIDDEN HS"
Sheets("REG-HS").Select
Range("A1").Select
End Sub



However this only works when there are exactly 430 rows. I need this work regardless of how many rows there are, so I tried to make this dynamic by altering the code to :

' h Macro
' h
'
' Keyboard Shortcut: Ctrl+h
'
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$AA$2:$AA$" & Range("AA" & Rows.Count).End(xlUp).Rows) _
, ActiveSheet.Range("$I$2:$K$" & Range("K" & Rows.Count).End(xlUp).Rows), False, True, , "", False, False, _
False, False, , False
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "REG-HS"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "HIDDEN HS"
Sheets("REG-HS").Select
Range("A1").Select
End Sub


But.... now it doesn't work. Anyone have any idea that might work?

 

[steve the fish]

Its not .Rows but .Row

 

[JamisonHowell]

-Face palm-
Thank you so much. Sometimes it's the most obvious issues. ...