I have an ineffective and long code that needs optimising or trimming-CodePudding

Well this is obviously part of the code but this part I keep getting lots of different error and as a beginner I don't really understand how I can adress them.

I'm in my workbook and sheet, so these are the first parameters I would like to exclude here but I get finding my self generating error rather then shorter code and so I'm asking for some help/guidens.

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

Lots of different ways to select and so but I probably don't know proper commands for typing only in VBA so I record macros and do different experiments.

CodePudding user response：

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

CodePudding user response：

Sort an Excel Table

Option Explicit

Sub SortHeaderSVA()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("PR11_P3")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("PR11_P3_Tabell")
    Dim tlc As ListColumn: Set tlc = tbl.ListColumns("SVA")
    
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add _
            Key:=tlc.Range, _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

Sub SortHeaderSVANoVariables()
    
    With ThisWorkbook.Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").Sort
        .SortFields.Clear
        .SortFields.Add _
            Key:=.Parent.ListColumns("SVA").Range, _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub