# Macros/Calc/ba028

< Macros‎ | Calc
Other languages:
English • ‎español • ‎italiano

## 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
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())