An advanced filter supports up to eight filter conditions, as opposed to the three supported by the simple filter. The criteria for an advanced filter is stored in a sheet. The first step in creating an advanced filter is entering the filter criteria into the spreadsheet.
- Select an empty space in the Calc document. The empty space may reside in any sheet in any location in the Calc document.
- Duplicate the column headings from the area to be filtered into the area that will contain the filter criteria.
- Enter the filter criteria underneath the column headings (see Table 4). The criterion in each column of a row is connected with AND. The criteria from each row are connected with OR.
Table 4. Example advanced filter criteria
| Name
| Test 1
| Test 2
| Quiz 1
| Quiz 2
| Average
| Grade
|
| ="Andy"
|
| >80
|
|
|
|
|
|
|
|
|
|
| <80
|
|
 | Define named ranges to reference your advanced filter criteria and any destination ranges for filtered data. Each appropriately configured named range is available in drop down list boxes in the Advanced Filter dialog.
|
After creating one or more sets of filter criteria, apply an advanced filter as follows:
- Select the sheet ranges that contain the data to filter.
- Use Data > Filter > Advanced Filter to open the Advanced Filter dialog.
- Select the range containing the filter criteria and any other relevant options.
- Click OK.
Apply an advanced filter using a previously defined named range.
Applying an advanced filter using a macro is simple (see Listing 8). The cell range containing the filter criteria is used to create a filter descriptor, which is then used to filter the range containing the data.
Listing 8. Use an advanced filter.
Sub UseAnAdvancedFilter()
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:G3")
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 I want to filter
oSheet = ThisComponent.getSheets().getByIndex(0)
oDataRange = oSheet.getCellRangeByName("A1:G16")
oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
oDataRange.filter(oFiltDesc)
End Sub
Change properties on the filter descriptor to change the behavior of the filter (see Table 5).
The filter created in Listing 8 filters the data in place. Modify the OutputPosition property to specify a different output position (see Listing 9). The filter descriptor must be modified before the filter is applied.
Table 5. Advanced filter properties.
| Property
| Comment
|
| ContainsHeader
| Boolean (true or false) that specifies if the first row (or column) contains headers which should not be filtered.
|
| CopyOutputData
| Boolean that specifies if the filtered data should be copied to another position in the document.
|
| IsCaseSensitive
| Boolean that specifies if the case of letters is important when comparing entries.
|
| Orientation
| Specifies if columns (com.sun.star.table.TableOrientation.COLUMNS) or rows (com.sun.star.table.TableOrientation.ROWS) are filtered.
|
| OutputPosition
| If if CopyOutputData is True , specifies the position where filtered data are to be copied.
|
| SaveOutputPosition
| Boolean that specifies if the OutputPosition position is saved for future calls.
|
| SkipDuplicates
| Boolean that specifies if duplicate entries are left out of the result.
|
| UseRegularExpressions
| Boolean that specifies if the filter strings are interpreted as regular expressions.
|
Listing 9. Copy filtered results to a different location.
REM Copy the output data rather than filter in place.
oFiltDesc.CopyOutputData = True
REM Create a CellAddress and set it for Sheet3,
REM Column B, Row 4 (remember, start counting with 0)
Dim x As New com.sun.star.table.CellAddress
x.Sheet = 2
x.Column = 1
x.Row = 3
oFiltDesc.OutputPosition = x
(Warning, advanced material.) The OutputPosition property returns a copy of a struct. Because a copy is returned, it is not possible to set the individual values directly. For example, oFiltDesc.OutputPosition.Row = 2 does not work (because you set the Row on the copy to 2, but do not change the original).
