Filtering Multiple Columns and Filtering with Regular Expressions

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Macros
• General Macros
• Macros for Writer
• Macros for Calc
• Macros for Impress
• Macros for Draw
• Macros for Base
• Macros for Math
• Basic/Python

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.