Macros/Calc/ba029

    From The Document Foundation Wiki
    < Macros‎ | Calc


    Summary

    This page provides a LibreOffice Basic macro procedure that creates an advanced filter. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

    Description

    Applying an advanced filter with a macro works similarly to setting up a standard filter. The key difference is that the cell range containing the filter criteria is used to create the filter descriptor. The macro procedure below demonstrates how this is done using the createFilterDescriptorByObject method. It uses a class grade sheet in cells $Sheet1.A1:H11 as the data range and cells $Sheet2.A1:H3 as the criteria range. The linked spreadsheet contains such a data range and criteria range.

    Code

    Sub AdvancedRangeFilter()
      Dim oSheet     'A sheet from the Calc document.
      Dim oRanges    'The NamedRanges property.
      Dim oCritRange 'Range that contains the filter criteria.
      Dim oDataRange 'Range that contains the data to filter.
      Dim oFiltDesc  'Filter descriptor.
    
      REM Range that contains the filter criteria
      oSheet = ThisComponent.getSheets().getByIndex(1)
      oCritRange = oSheet.getCellRangeByName("A1:H3")
    
      REM You can also obtain the range containing the 
      REM filter criteria from a named range.
      REM oRanges = ThisComponent.NamedRanges
      REM oRange = oRanges.getByName("AverageLess80")
      REM oCritRange = oRange.getReferredCells()
    
      REM The data that you want to filter
      oSheet = ThisComponent.getSheets().getByIndex(0)
      oDataRange = oSheet.getCellRangeByName("A1:H11")
    
      oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
      oDataRange.filter(oFiltDesc)
    End Sub

    This Calc spreadsheet contains the above LibreOffice Basic code.