Assigning Table/Header names in Sort Functions

Question

I have a code that works when I assign ranges manually, but this is a bit cumbersome especially when it's unclear if the sheet will be modified and therefore make the values in the code incorrect.

My original code

Const ProcName As String = "Filter_Ascending_Descending"
On Error GoTo Whoa

Dim WsCP As Worksheet: Set WsCP = Sheets("Cross Platform Database")
Dim WsDND As Worksheet: Set WsDND = Sheets("DO NOT DELETE")
Dim Header As Variant: Header = WsCP.Range("D36").Value
Dim CriteriaOp As Variant: CriteriaOp = WsCP.Range("C38").Value     '<< This will be the Data Value
Dim Criteria As Variant: Criteria = WsCP.Range("D38").Value         '<< This will be the Data Value
Dim Order As Variant: Order = WsCP.Range("C36").Value               '<< Expected to be Ascending/Descending
Dim OrderHeader As Variant: OrderHeader = "Table1[[#Headers],[Sales]]"

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'This code will sort the data in the relevant column according to the user selection
    If WsDP.Range("C26") = "Ascending" Then
        Sheets("Cross Platform Database").Range("S24:S71499").Sort _
          Key1:=Sheets("Cross Platform Database").Range("S23"), Order1:=xlAscending, Header:=xlYes

    ElseIf WsDP.Range("C26") = "Descending" Then
        Sheets("Cross Platform Database").Range("S24:S71499").Sort _
          Key1:=Sheets("Cross Platform Database").Range("S23"), Order1:=xlDescending, Header:=xlYes
    Else
        
    End If

SafeExit:
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    
    Exit Sub

Whoa:
    Debug.Print "'" & ProcName & "' Run-time error '" _
      & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume SafeExit

End Sub

I tried to change all ranges to table names but my code jumps to error and returns

error 5 "Invalid procedure call or arguement"

'This code will sort the data in the relevant column according to the user selection
If Order = "Ascending" Then
    WsCP.ListObjects("Table1").Range("Table1[Sales]").Sort _
      Key1:=WsCP.Range(OrderHeader), Order1:=xlAscending, Header:=xlYes

You have Set WsCP = Sheets("Cross Platform Database") but the order is wsDP.Range("C26") so what sheet is wsDP? — CDP1802
– CDP1802, Commented Jan 19, 2022 at 12:14
You need to include the header row: "S23:S71499" and you can (explicitly) use the same as the Key1 parameter. Also, .Range("Table1[Sales]") doesn't include the headers. You need to explain if you want to sort the entire table or the column independently. — VBasic2008
– VBasic2008, Commented Jan 19, 2022 at 12:28

VBasic2008 · Accepted Answer · 2022-01-19 12:56:29Z

1

Sort an Excel Table

Option Explicit

Sub TestSortTable()

    Dim WsCP As Worksheet: Set WsCP = Worksheets("Cross Platform Database")
    Dim AscDesc As String: AscDesc = CStr(WsCP.Range("C26").Value) ' unclear
    
    Dim tbl As ListObject: Set tbl = WsCP.ListObjects("Table1")
    Dim lcl As ListColumn: Set lcl = tbl.ListColumns("Sales")
    
    Select Case LCase(AscDesc)
    Case "ascending"
        tbl.Range.Sort _
            Key1:=lcl.Range, Order1:=xlAscending, Header:=xlYes
    Case "descending"
        tbl.Range.Sort _
            Key1:=lcl.Range, Order1:=xlDescending, Header:=xlYes
    Case Else ' do nothing
    End Select

End Sub

answered Jan 19, 2022 at 12:56

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assigning Table/Header names in Sort Functions

1 Answer 1

Sort an Excel Table

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sort an Excel Table

Comments

Your Answer

Sign up or log in

Post as a guest

Related