[Solved] Advanced Filter Macro is Too Slow

[Math]

Greetings ,

         I'm working on a LibreOffice 5.4.4.2 file, whose approximate size is 30 MB .

         I am using a macro that filters the information in the "A3:CA5000" cells of the "2.1Pedidos para Fat" spreadsheet based on criteria established in the "A1:CA2" cells that are placed in the "Data_Pedidos" worksheet, after Filtering, the values paste in the "Pedidos_Pendentes" worksheet from cell "A1" .

         But, is too slow to do the operation, the macro is running more than an hour to complete the operation .

         need to find an alternative to Minimize the time the macro takes to do the operation .

        
hugs.

[RusselB]

Have you timed how long it takes to do the same operation using the manual controls (ie: using Data -> Filter) rather than the macro?

[John_Ha]

Please upload small example files showing the filter rules and the data being filtered so that what you are doing can be analysed.

Also, please precisely explain your objective as there may be a better or faster way of doing what you want to do. For example, can you use any of the Database-specific functions described on page 339 of the Calc Guide?

Are any of the example filter macros given in Chapter 13 - Calc as a Simple Database of any help? For example, Listing 16 is

A simple sheet filter using two columns

Listing 16 demonstrates a more advanced filter that filters two columns and uses regular expressions. Some unexpected behavior occurred while working with Listing 16. Although you can create a filter descriptor using any sheet cell range, the filter applies to the entire sheet.

Code: Select all

Sub SimpleSheetFilter_2()
  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("E12:G19")

  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.
    .IsNumeric = False ' Use a string, not a number.
    .StringValue = "b.*" ' Everything starting with b.
    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
  End With
  REM Setup a field that requires both conditions and
  REM this new condition requires a value greater or
  REM equal to 70.
  With oFields(1)
    .Connection = com.sun.star.sheet.FilterConnection.AND
    .Field = 5 ' Filter column F.
    .IsNumeric = True ' Use a number
    .NumericValue = 70 ' Values greater than 70
    .Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
  End With 

  oFilterDesc.setFilterFields(oFields())
  oFilterDesc.ContainsHeader = False
  oFilterDesc.UseRegularExpressions = True
  oSheet.filter(oFilterDesc)
End Sub

Listing 17 says

Applying an advanced filter using a macro is simple (see Listing 17). 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.

Code: Select all

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 you want to filter

  oSheet = ThisComponent.getSheets().getByIndex(0)
  oDataRange = oSheet.getCellRangeByName("A1:G16")

  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oDataRange.filter(oFiltDesc)
End Sub

[Math]

[Solved]

> This topic has been solved .


hugs .

[Villeroy]

Please upload small example files showing the filter rules and the data being filtered so that what you are doing can be analysed.

Why? He only wants someone to do his work.

[Math]

I used the macro sub UseAnAdvancedFilter of the sr. John_Ha

thank you very much sr. John_Ha


friend hug .

[Villeroy]

Magic spells copied from the internet

[John_Ha]

I used the macro sub UseAnAdvancedFilter of the sr. John_Ha

When all else fails reading the manual is often your best bet.