Sort a Table Using Multiple Columns

    From The Document Foundation Wiki
    < Macros‎ | Calc


    Other languages:

    Summary

    This page provides a LibreOffice Basic macro procedure that sorts a table based on the values in multiple columns. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

    Description

    A range can be sorted using up to three columns or rows in a macro. Sorting with extra columns or rows is as easy as adding extra sort fields to a sort descriptor. The macro below uses the class grade sheet in cells $Sheet1.A1:H11 of the linked spreadsheet to illustrate how to sort by two columns. The records are sorted in ascending order, first by Quiz #1 scores and then by Quiz #2 scores.

    Code

    Sub SortByQuizScores
      Dim oSheet
      Dim oRange
      Dim oSortFields(1) as new com.sun.star.util.SortField
      Dim oSortDesc(0) as new com.sun.star.beans.PropertyValue
    
      oSheet = ThisComponent.Sheets(0)
    
      REM Set the range on which to sort
      oRange = oSheet.getCellRangeByName("A1:H11")
    
      REM Sort by the Quiz #1 field in the range
      oSortFields(0).Field = 4
      oSortFields(0).SortAscending = True
      oSortFields(0).FieldType = com.sun.star.util.SortFieldType.NUMERIC
    
      REM Sort by the Quiz #2 field in the range
      oSortFields(1).Field = 5
      oSortFields(1).SortAscending = True
      oSortFields(1).FieldType = com.sun.star.util.SortFieldType.ALPHANUMERIC
    
      REM Set the sort fields to use
      oSortDesc(0).Name = "SortFields"
      oSortDesc(0).Value = oSortFields()
      
      REM Now sort the range!
      oRange.Sort(oSortDesc())
    End Sub

    This Calc spreadsheet contains the above LibreOffice Basic code.