Filtering Multiple Columns and Filtering with Regular Expressions

    From The Document Foundation Wiki
    < Macros‎ | Calc

    Other languages:


    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.


    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.


    Sub SimpleRangeFilter()
      Dim oSheet          ' Sheet to filter.
      Dim oRange          ' Range to be filtered.
      Dim oFilterDesc     ' Filter descriptor.
      Dim oFields(1) As New
      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 =
      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 =
        .Field = 6              ' Filter column G (Test #1 grades).
        .IsNumeric = True       ' Use a number
        .NumericValue = 90      ' Scores less than 90
        .Operator =
      End With
      oFilterDesc.ContainsHeader = True
      oFilterDesc.UseRegularExpressions = True
    End Sub

    This Calc spreadsheet contains the above LibreOffice Basic code.