Macro Sort on two Fields

[howard]

I have text in Col F (various categories) from row 2 onwards (Headers in in row1) and values in Col G


I would like a macro to sort Col F from row2 onwards in Ascending order and then to sort the values for each of the categories from Largest to smallest

I have written code to do this, but get a run time error 1004 "Sort method of range class failed"

 Selection.Sort Key2:=Range("F2"), Order1:=xlDescending

 Sub Sort_Largest()
Sheets(3).Select
Dim Lr As Long
Lr = Cells(Rows.Count, "C").End(xlUp).Row
Range("A2:H" & Lr).Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending
Selection.Sort Key2:=Range("F2"), Order1:=xlDescending


End Sub

It would be appreciated if someone could kindly amend my code

 

[MARK858]

Possibly...

[CODE]Sub Sort_Largest()
    Sheets(3).Select
    Dim Lr As Long
    Lr = Cells(Rows.Count, "C").End(xlUp).Row
    Range("A2:H" & Lr).Sort Key1:=Range("G2"), Order1:=xlAscending, _
    Key2:=Range("F2"), Order1:=xlDescending
End Sub

[/CODE]

 

[howard]

Thanks for the help. Code works but does not goes me the desired result


I have recorded the macro to sort and it gives me the desired result


Kindly neaten the code

 Sub Sort_Data()
Sheets(3).Select

'
    Columns("A:H").Select
    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:= _
        Range("G1:G13935"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:= _
        Range("F1:F13935"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Invoices").Sort
        .SetRange Range("A1:H13935")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
  
End Sub

 

[MARK858]

What do you want "neatened" with the code, there is nothing wrong with it?

 

[howard]

Thanks for the reply

I have streamlined it as follows:

 Sub Sort_Data()
Sheets(3).Select
    Dim Lr As Long
    Lr = Cells(Rows.Count, "C").End(xlUp).Row
   ActiveWorkbook.Worksheets(3).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(3).Sort.SortFields.Add Key:= _
        Range("G1:G" & Lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets(3).Sort.SortFields.Add Key:= _
        Range("F1:F" & Lr), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(3).Sort
        .SetRange Range("A1:H" & Lr)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With