Macro to Sort Variable amounts of Data

L

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.
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top