excelvbasortingexcel-tableslistobject

Sort an Excel Table


In this code to sort a table I get different errors.

In my sheet, these are the first parameters I would like to exclude.

Sub SortHeaderSVA()

    Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").ListColumns("SVA").Range.Cells(1).Select
    'cant do anything of the soriting or so if I dont do above which is,
    ' well has to be a simpler way to select a header in a table
    ' that is in the current sheet and active workbook..?

    ActiveWorkbook.Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").Sort. _
        SortFields.Clear
        'also wierd, why dose the sort needs to be cleard first 
        ' if there is no value earlier?

    ActiveWorkbook.Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").Sort. _
        SortFields.Add Key:=Range("PR11_P3_Tabell[[#All],[SVA]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

     With ActiveWorkbook.Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

I record macros and do different experiments.


Solution

  • Don't select Objects unless absolutely necessary. Remove this line:

    Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").ListColumns("SVA").Range.Cells(1).Select

    SortFields.Clear is used to ensure that the previous settings are cleared. After all we are adding sorts to a collection. You can test this by commenting out the SortFields.Clear line and running it several times. Next from the Ribbon open the Data Tab and choose Sort. You'll see that there are multiple sorts that are going to be ran whenever you call the .Sort method.

    Sort Dialog

    Public Sub SortHeaderSVA()
    
        With PR11_P3_Tabell.Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=PR11_P3_Tabell.ListColumns("SVA").Range, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    
    Public Function wsPR11_P3() As Worksheet
        Set wsPR11_P3 = ThisWorkbook.Worksheets("PR11_P3")
    End Function
    
    Public Property Get PR11_P3_Tabell() As ListObject
        Set PR11_P3_Tabell = wsPR11_P3.ListObjects("PR11_P3_Tabell")
    End Property
    

    Addendum

    My original code declared the ListObject as a Property of the module. The code is auto generated from a code snippet that I have. I usually change the method signature from a Property to a Function for my post. Like this:

    Public Function  PR11_P3_Tabell() As ListObject
        Set PR11_P3_Tabell = wsPR11_P3.ListObjects("PR11_P3_Tabell")
    End Function