If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to refine this Macro
Hello,
with the help of this group I have created this Macro which I run from a button, but I need to pre select the range. Sub Sort_Fastest_Male_then_Female() ' ' Sort_Fastest_Male_then_Female Macro ' Macro recorded 6/06/2009 by Stephen Ditchfield ' ' Selection.Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range ("I7") _ , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _ xlSortNormal End Sub is there a way to modify it so I don't need to select the range then run the Macro. Can I just run the Macro and it finds the last used cell in I7 and sorts leaving the Header rows (1 to 6) in place? thanks again for all the help regards Ditchy Ballarat, Australia |
#2
|
|||
|
|||
how to refine this Macro
Can you pick out a column that's always used if that row has data?
Can you pick out a row that always has data in it (in the header???)? If yes, then you could use something like: Option Explicit Sub Sort_Fastest_Male_then_Female() Dim LastRow As Long Dim LastCol As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column With .Range("a7", .Cells(LastRow, LastCol)) .Cells.Sort key1:=.Columns(13), Order1:=xlDescending, _ Key2:=.Columns(9), Order2:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End With End With End Sub I used column A to determine the last row. And I use row 1 to determine the last column. Ps. Change the sheet name to match what you need, too. wrote: Hello, with the help of this group I have created this Macro which I run from a button, but I need to pre select the range. Sub Sort_Fastest_Male_then_Female() ' ' Sort_Fastest_Male_then_Female Macro ' Macro recorded 6/06/2009 by Stephen Ditchfield ' ' Selection.Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range ("I7") _ , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _ xlSortNormal End Sub is there a way to modify it so I don't need to select the range then run the Macro. Can I just run the Macro and it finds the last used cell in I7 and sorts leaving the Header rows (1 to 6) in place? thanks again for all the help regards Ditchy Ballarat, Australia -- Dave Peterson |
#3
|
|||
|
|||
how to refine this Macro
Range("A7:M" & [I65536].End(xlup)).Sort _
Key1:=Range("M7"), _ Order1:=xlDescending, _ Key2:=Range("I7"), _ Order2:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal Now you can probably simpliy this to Range("A7:M" & [I65536].End(xlup)).Sort _ Key1:=Range("M7"), _ Order1:=xlDescending, _ Key2:=Range("I7"), _ Order2:=xlAscending, _ Header:=xlNo Here I am assuming that you data goes from column A to M. I have laid it out this way so that its easier to read. -- If this helps, please click the Yes button. Cheers, Shane Devenshire " wrote: Hello, with the help of this group I have created this Macro which I run from a button, but I need to pre select the range. Sub Sort_Fastest_Male_then_Female() ' ' Sort_Fastest_Male_then_Female Macro ' Macro recorded 6/06/2009 by Stephen Ditchfield ' ' Selection.Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range ("I7") _ , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _ xlSortNormal End Sub is there a way to modify it so I don't need to select the range then run the Macro. Can I just run the Macro and it finds the last used cell in I7 and sorts leaving the Header rows (1 to 6) in place? thanks again for all the help regards Ditchy Ballarat, Australia |
#4
|
|||
|
|||
how to refine this Macro
On Jun 6, 3:53*pm, Shane Devenshire
wrote: * * Range("A7:M" & [I65536].End(xlup)).Sort _ * * * * Key1:=Range("M7"), _ * * * * Order1:=xlDescending, _ * * * * Key2:=Range("I7"), _ * * * * Order2:=xlAscending, _ * * * * Header:=xlNo, _ * * * * OrderCustom:=1, _ * * * * MatchCase:=False, _ * * * * Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal, _ * * * * DataOption2:=xlSortNormal Now you can probably simpliy this to * * Range("A7:M" & [I65536].End(xlup)).Sort _ * * * * Key1:=Range("M7"), _ * * * * Order1:=xlDescending, _ * * * * Key2:=Range("I7"), _ * * * * Order2:=xlAscending, _ * * * * Header:=xlNo Here I am assuming that you data goes from column A to M. *I have laid it out this way so that its easier to read. -- If this helps, please click the Yes button. Cheers, Shane Devenshire " wrote: Hello, with the help of this group I have created this Macro which I run from a button, but I need to pre select the range. Sub Sort_Fastest_Male_then_Female() ' ' Sort_Fastest_Male_then_Female Macro ' Macro recorded 6/06/2009 by Stephen Ditchfield ' ' * * Selection.Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range ("I7") _ * * * * , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ * * * * Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _ * * * * xlSortNormal End Sub is there a way to modify it so I don't need to select the range then run the Macro. Can I just run the Macro and it finds the last used cell in I7 and sorts leaving the Header rows (1 to 6) in place? thanks again for all the help regards Ditchy Ballarat, Australia- Hide quoted text - Thanks for the quick response, I am having trouble with the answers that you have given, when I try Dave's I get an error (subscript out of range) when I try shane's I get an error (400) not sure how to proceed? I have a link to the finished product that i need here, which will explain it better than me. http://ballaratharriers.com/data/res...esidents09.pdf The top 6 rows are always the header thanks again for your help regards Ditchy Ballarat, Australia - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|