how to refine this Macro

[stephen.ditchfield]

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]

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.

 

[Shane Devenshire]

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.

 

[stephen.ditchfield]

    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

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/results/allraces2009/bh-presidents09.pdf

The top 6 rows are always the header
thanks again for your help
regards
Ditchy
Ballarat, Australia