VBA Sorting Inquiry

[DonEB]

I've been slowly working my way thru an old excel spreadsheet which is loaded with macro's in an attempt to simplify them and make them more efficient. My attempt to simplify a sort has been unsuccessful and, despite many searches on the internet, I am unable to determine where I'm going wrong.

Here is my original code which does work.

  Range("F43:I72").Select
    ActiveWorkbook.Worksheets("AllWeeks").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("AllWeeks").Sort.SortFields.Add Key:=Range( _
        "h43:h72"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal                                                                        
    ActiveWorkbook.Worksheets("AllWeeks").Sort.SortFields.Add Key:=Range( _
        "G43:G72"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal                                                                        
    ActiveWorkbook.Worksheets("AllWeeks").Sort.SortFields.Add Key:=Range( _
        "I43:I72"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal                                                                        
    With ActiveWorkbook.Worksheets("AllWeeks").Sort
        .SetRange Range("F43:I72")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Here is the new code I've been trying to debug and implement.

Worksheets("AllWeeks").Sort.SortFields.Clear
Range("F43:I72").Sort Key1:=Range("H43"), Key2:=Range("G43"), Key3:=Range("I43"), Order1:=xlAscending, Order2:=xlAscending, Order3:=xlAscending, Header:=x1No

Any thoughts on what I'm doing wrong would be greatly appreciated.

Thanks,
Don

 

[Alex Blakenburg]

The only error I can find with your code is that the xlNo has a "1" (numeric one) instead of the letter l (L).

Header:=x1No
'/sbe
Header:=xlNo

I am assuming you really don't have column headings in Row 43, otherwise the Header should be xlYes.

A more common way of laying it out would be below to have the Order expression paired with the Key expressions, although you normally would probably not hard code the end of the range ie 72 but find the last row in the code.

Sub testSort()

    Dim rng As Range
    Dim ws As Worksheet
  
    Set ws = Worksheets("AllWeeks")
    Set rng = ws.Range("F43:I72")

    ws.Sort.SortFields.Clear
  
    With rng
        .Sort Key1:=ws.Range("H43"), Order1:=xlAscending, _
                Key2:=ws.Range("G43"), Order2:=xlAscending, _
                Key3:=ws.Range("I43"), Order3:=xlAscending, _
                Header:=xlNo
    End With

End Sub

 

[DonEB]

The only error I can find with your code is that the xlNo has a "1" (numeric one) instead of the letter l (L).

Header:=x1No
'/sbe
Header:=xlNo

I am assuming you really don't have column headings in Row 43, otherwise the Header should be xlYes.

A more common way of laying it out would be below to have the Order expression paired with the Key expressions, although you normally would probably not hard code the end of the range ie 72 but find the last row in the code.

Sub testSort()

    Dim rng As Range
    Dim ws As Worksheet
 
    Set ws = Worksheets("AllWeeks")
    Set rng = ws.Range("F43:I72")

    ws.Sort.SortFields.Clear
 
    With rng
        .Sort Key1:=ws.Range("H43"), Order1:=xlAscending, _
                Key2:=ws.Range("G43"), Order2:=xlAscending, _
                Key3:=ws.Range("I43"), Order3:=xlAscending, _
                Header:=xlNo
    End With

End Sub

Thank you very much for your quick reply... and for your eagle eyes!! Your recommended fix and your proposed use of the "common" code worked great.

Your advice about not hard coding the end range is noted and sometime in the near future I hope to understand how to accomplish that which you are suggesting. In this instance, I am dealing with a fixed range of data so I'm concerned about this range changing.

Thanks again for your help and guidance. It is much appreciated.

 

[Alex Blakenburg]

You're welcome. Glad I could help.