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.

--
Regards,
Tom Ogilvy


"loophole21" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> loophole21
> ------------------------------------------------------------------------
> loophole21's Profile:
> http://www.excelforum.com/member.php...o&userid=36779
> View this thread: http://www.excelforum.com/showthread...hreadid=564949
>

[loophole21]

Thanks so much, you have been a great help!