CODEWITHSUNDEEP
excelvbalistobject
Dumitru Daniel
Dumitru Daniel

Reputation: 549

Excel get sorted data from a ListObject without sorting it

I'm trying to get sorted/filtered data from an Excel ListObject into an array I can work with. Because of the reason below, i'm looking for alternatives to ADO query. I found LO.DataBodyRange.Sort (see code at the end), but i'm not sure how I can use it to retrieve the data sorted into an array withouth modifying the table (which is super important because sorting a listObject causes named ranges inside the table to lose their correct refersTo).

If you have any experience with this please let me know how I could do that.

Reason:

I am currently using an ADO query to get the data sorted and filtered, but there are 2 reasons why i'm looking for alternatives:

  1. It is highly dependent on "Dynamic Data Exchange Server Lookup" setting in Excel, setting that was reset to unchecked with last Excel update and this caused the query to crash on objRecordset.open.
objRecordset.Open strSQL, _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
  1. Also after the latest Excel update, the query went from a 1s runtime to 3-5 seconds, and for repeated actions that would be bad.
Sub getTableData()
    
    Dim LO As ListObject
    Dim dResult As New Scripting.Dictionary, dKey As Variant, arrItem() As Variant, arrI As ListRow, I As Long
    Dim arrDataBodyRange()
    
    Set LO = wsInputsDb.ListObjects("tblInputs")
    
    dKey = "headers"
    arrItem = LO.HeaderRowRange.Value
    
    dResult.Add dKey, arrItem
    
    arrDataBodyRange = LO.DataBodyRange.Value
    'arrDataBodyRange = LO.DataBodyRange.Sort("SubSection order").Value
    
'    I = 0
'    For Each arrI In LO.ListRows
'        I = I + 1
'        dResult.Add I, arrI.Range.Value
'    Next
    
End Sub

Upvotes: 0

Views: 467

Answers (1)

Ike
Ike

Reputation: 13024

You can use the worksheet function SORT. You have to provide the column index by which the listobject should be sorted.

With lo
    arrData = Application.WorksheetFunction.Sort( _
        .DataBodyRange, .ListColumns("Subsection order").Index, 1 _
        )
End With

I think you need Excel 365 for this.

Update: double sorting

With Application.WorksheetFunction
    arrData = .Sort(.Sort(lo.DataBodyRange, lo.ListColumns("subsection order").Index, 1), _
        lo.ListColumns("second column").Index, 1)
End With
```

Upvotes: 2

Related Questions