Add Sort Function To A Varying Length Spreadsheet

[TkdKidSnake]

Hi all,

I am trying to add a sort function to a spreadsheet that varies in length, however the code stops when it gets to the .Apply section, does anyone know how to fix this?

See the code below:

LastRow = sht.Range("A1").CurrentRegion.Rows.Countsht.Range("A2", sht.Range("k" & LastRow)).Select
'
ActiveWorkbook.Worksheets("RawData").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("RawData").Sort.SortFields.Add Key:=Range( _
        "A2" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RawData").Sort.SortFields.Add Key:=Range( _
        "D2" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RawData").Sort.SortFields.Add Key:=Range( _
        "C2" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RawData").Sort.SortFields.Add Key:=Range( _
        "G2" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RawData").Sort.SortFields.Add Key:=Range( _
        "E2" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("RawData").Sort
        .SetRange Range("A2" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Any help you can give on this would be greatly appreciated

Thanks in advance

 

[Fluff]

Change the Keys to read A1, D1 etc
and then use

With ActiveWorkbook.Worksheets("RawData").Sort
    .SetRange Range("[COLOR=#ff0000]A2:K" & LastRow[/COLOR])
    .Header = [COLOR=#ff0000]xlYes[/COLOR]
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

 

[TkdKidSnake]

Excellent work thank you for this, its so obvious now you've said that much appreciated.

Change the Keys to read A1, D1 etc
and then use

With ActiveWorkbook.Worksheets("RawData").Sort
    .SetRange Range("[COLOR=#ff0000]A2:K" & LastRow[/COLOR])
    .Header = [COLOR=#ff0000]xlYes[/COLOR]
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

 

[Fluff]

You're welcome & thanks for the feedback