Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort Variable amounts of Data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort Variable amounts of Data
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Sort Variable amounts of Data
Thanks so much, you have been a great help! -- loophole21 ------------------------------------------------------------------------ loophole21's Profile: http://www.excelforum.com/member.php...o&userid=36779 View this thread: http://www.excelforum.com/showthread...hreadid=564949 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to sort variable data range fails | Excel Discussion (Misc queries) | |||
Macro to Sort A-Z a variable range of cells | Excel Discussion (Misc queries) | |||
Macro with Variable Data | Excel Worksheet Functions | |||
How do I use a variable in a sort macro | Excel Worksheet Functions | |||
Macro - Data Sort -Variable Selection | Excel Discussion (Misc queries) |