This page provides a LibreOffice Basic macro code snippet that copies advanced filter results to a different location. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.
The results of an advanced filter can be extracted to a different position using the OutputPosition property. Copying results in this way eliminates the need for Calc to hide rows that do not match search criteria, which it would normally do if you filter in-place.
The macro code snippet below demonstrates how to copy filter results to a different location. Note that the filter descriptor must first be modified before these filter settings are applied.
To demonstrate the operation of this snippet, take the code for the AdvancedRangeFilter macro procedure on the Create Advanced Filter page and insert the code given below, just before the filter method is called. The linked spreadsheet contains the modified version of the AdvancedRangeFilter macro procedure and it uses a class grade sheet in cells $Sheet1.A1:H11 as the data range and cells $Sheet2.A1:H3 as the criteria range. The filtered results are placed in cells $Sheet1.B13:I19.
1 REM Copy the output data rather than filter in place. 2 oFiltDesc.CopyOutputData = True 3 4 REM Create a CellAddress and set it for Sheet1 5 REM Column B, Row 13 (remember, start counting with 0) 6 Dim outputCell As New com.sun.star.table.CellAddress 7 outputCell.Sheet = 0 8 outputCell.Column = 1 9 outputCell.Row = 12 10 oFiltDesc.OutputPosition = outputCell
This Calc spreadsheet contains the above LibreOffice Basic code.