Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting multiple range using a macro
Hi,
I am tyring to use a macro to sort a group of ranges C1:D20, C21:D40, ......C31:D400. i need to sort each of these ranges based on entries in coulmn C. There are 20 ranges here. Heres the code that I use to sort the first two ranges..... Range("C1:D20").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("C21:D40").Select Selection.Sort Key1:=Range("C21"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal but i would prefer to use a for loop so that in case i change the spreadsheet later it would be easy for me to change the code too........ Dhanush --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting multiple range using a macro
Dhanush,
I have looked at your coding and am a little unsure how you are sorting your ranges as they seem to over lap. I have not changed your coding, just added to it in order to provide you with the loop function. Sub Sort() ' Dim rwIndex, ColIndex ' rwIndex = 1 ColIndex = 3 ' Do Until Range("C" & rwIndex) = "" rwIndex = rwIndex + 1 Loop Range(Cells(1, 3), Cells(rwIndex, ColIndex)).Select ' Range("C1:D20").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("C21:D40").Select Selection.Sort Key1:=Range("C21"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' Range("A1").Select End Sub It starts from C1 and continues looping until there is an empty cell in Column C. Hence ColIndex being equal to 3. Hope that this helps. Kiza --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting multiple range using a macro
Hi Kiza,
I already have lots of space in each of the ranges. I mean not al range have contents in all their cells hence I am not sure as how t change your code suggestion to suit my needs...... the problem is tha i am not all that good with VB..... Thanks for yo suggestions. Hi Don Guillett, I tried to copy your code into the macro and i am getting an erro statement "Can't execute code in break mode" heres the code Range("A651:A670").Select For Each c In Selection 'MsgBox c x = Left(c, InStr(c, ":") - 1) 'MsgBox x 'Range("C1:D20").Sort Key1:=Range("C1"), Order1:=xlAscending Range(c).Sort Key1:=Range(x), Order1:=xlAscending Next c A651:A670 is where i have stored all the ranges that need to b sorted... that is A651 = C1:D20 -- A652 = C21:D40 etc For now i am individualy sorting each range in the code......But woul like to make the code short my looping Dhanus -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting multiple range using a macro
On the toolbar just touch your reset button (blue square on mine) or
Runreset then Sub sortfromloop() For Each c In Range("A651:A670") 'MsgBox c x = Left(c, InStr(c, ":") - 1) 'MsgBox x 'Range("C1:D20").Sort Key1:=Range("C1"), Order1:=xlAscending Range(c).Sort Key1:=Range(x), Order1:=xlAscending Next c End Sub -- Don Guillett SalesAid Software "onlinepredator " wrote in message ... Hi Kiza, I already have lots of space in each of the ranges. I mean not all range have contents in all their cells hence I am not sure as how to change your code suggestion to suit my needs...... the problem is that i am not all that good with VB..... Thanks for you suggestions. Hi Don Guillett, I tried to copy your code into the macro and i am getting an error statement "Can't execute code in break mode" heres the code Range("A651:A670").Select For Each c In Selection 'MsgBox c x = Left(c, InStr(c, ":") - 1) 'MsgBox x 'Range("C1:D20").Sort Key1:=Range("C1"), Order1:=xlAscending Range(c).Sort Key1:=Range(x), Order1:=xlAscending Next c A651:A670 is where i have stored all the ranges that need to be sorted... that is A651 = C1:D20 -- A652 = C21:D40 etc For now i am individualy sorting each range in the code......But would like to make the code short my looping Dhanush --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting range | Excel Discussion (Misc queries) | |||
Sorting a Range | Excel Discussion (Misc queries) | |||
Sorting range multiple areas | Excel Programming | |||
Sorting range multiple areas | Excel Programming | |||
Sorting Range Help | Excel Programming |