Macro to Sort Variable amounts of Data

[loophole21]

Hi, this is my first time trying to create a macro and Im stuck. I want
to create a macro in an excel template, that will sort a table of data.
At the end of the table there are some calculations that I do not want
to sort. The problem I seem to have is that each document I create from
the template will have a different amount of data in it. Thus If my
original macro sorts information in rows 8-53 for example, and in a
document created from the template I only have 10 rows of data, the
macro will still attempt to sort up to row 50, and thus it attempts to
sort the calculations at the end, and it gets messed up.

Here is the Macro Code:

Sub SortColor()
'
' SortColor Macro
' Macro recorded 7/12/2006 by Indra S.
'

'
Range("A8:I53").Select
Selection.Sort Key1:=Range("A8"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The range "A8:I53", is the maximum amount of data I would need to sort.
But lets say I only have data in "A8:I30", After I delete rows 31-53,
the range in the macro stays the same, and all my calculations at the
end are sorted.

My question then is, How can I get the macro to sort a variable amount
of rows? Is this even Possible? Am I being clear enough in my
explanation? Any other suggestions?

Thanks.

 

[Tom Ogilvy]

Sub SortColor()
'
' SortColor Macro
' Macro recorded 7/12/2006 by Indra S.
'

'
Range("A8", Range("A8").End(xldown)).Resize(,9).Select
Selection.Sort Key1:=Range("A8"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

there must be at least one blank row betweent the end of your data and the
formulas.

 

[loophole21]

Thanks so much, you have been a great help!