Selección de filas con rangos variables

Hola ¡

estoy tratando de hacer una macro en la cual me seleccione un rango variable de filas y después ordene de mayor a menor ciertas columnas y después aplico formulas con rangos variables.

esto lo que logre con la grabadora:

Sub MacroXXX()
'
' MacroXXX Macro
'
' Acceso directo: Ctrl+Mayus+V
'
Rows("9:9").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("9:33").Select
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range( _
"J10:J33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range( _
"U10:U33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range( _
"V10:V33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
.SetRange Range("A9:AE33")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-10
Range("L10").Select
ActiveCell.FormulaR1C1 = "=ROUND(RC[-2],0)"
Range("L10").Select
Selection.Copy
Range("J10").Select
Selection.End(xlDown).Select
Range("L34").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("L33").Select
Selection.End(xlUp).Select
Range("L10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=ROUND(RC[9],0)"
Range("L10").Select
Selection.Copy
Range("L11").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("L10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("U10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=ROUND(RC[10],0)"
Range("L10").Select
Selection.Copy
Range("L11").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("L10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("V10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=ROUND(RC[11],0)"
Range("L10").Select
Selection.Copy
Range("L11").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("L10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("W10").Select
ActiveWindow.SmallScroll Down:=-5
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L10").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

en la parte de Rows("9:33").Select me trunca la aplicación en caso de que mi hoja tenga más datos.

de antemano muchas gracias por tu valios aayuda