Filtering Multiple Columns and Filtering with Regular Expressions

    From The Document Foundation Wiki
    < Macros‎ | Calc


    Other languages:

    Summary

    This page provides a LibreOffice Basic macro procedure that filters two columns and uses regular expressions. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

    Description

    The macro procedure below demonstrates a filter that filters two columns and uses regular expressions. Note that the filter method is called on a range rather than its sheet in this example.

    As an example, the macro below filters a class grade sheet in cells $Sheet1.A1:H11 by Test #1 scores <= 90% and student names that begin with "B". The linked spreadsheet contains such a grade sheet.

    Code

    Sub SimpleRangeFilter()
      Dim oSheet          ' Sheet to filter.
      Dim oRange          ' Range to be filtered.
      Dim oFilterDesc     ' Filter descriptor.
      Dim oFields(1) As New com.sun.star.sheet.TableFilterField
      
      oSheet = ThisComponent.getSheets().getByIndex(0)
      oRange = oSheet.getCellRangeByName("A1:H11")
      
      REM If argument is True, creates an 
      REM empty filter descriptor.
      oFilterDesc = oRange.createFilterDescriptor(True)
    
      REM Setup a field to view cells with content that 
      REM start with the letter B.
      With oFields(0)   
        .Field = 0              ' Filter column A (Student names).
        .IsNumeric = False      ' Use a string, not a number.
        .StringValue = "b.*"    ' Every name starting with a B.
        .Operator = com.sun.star.sheet.FilterOperator.EQUAL
      End With
      REM Set up a field that requires at least one of the conditions.
      REM This new condition requires a value less than or 
      REM equal to 90.
      With oFields(1)
        .Connection = com.sun.star.sheet.FilterConnection.OR
        .Field = 6              ' Filter column G (Test #1 grades).
        .IsNumeric = True       ' Use a number
        .NumericValue = 90      ' Scores less than 90
        .Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL
      End With
    
      oFilterDesc.setFilterFields(oFields())
      oFilterDesc.ContainsHeader = True
      oFilterDesc.UseRegularExpressions = True
      oRange.filter(oFilterDesc)
    End Sub

    This Calc spreadsheet contains the above LibreOffice Basic code.