# How can I eliminate unwanted lines with a macro? [closed]

I have data that is exported from another program into an excel spreadsheet. I have a LibreOffice ODS spreadsheet that I am working with where the data from the initial export gets pasted in to Sheet1, some calculations are made, then the data from two specific columns on the Sheet1 gets copied to Sheet2 (including a column of the original data and the column with the calculation results.

My problem is that there are lines within the initial data that gets copied that I don't need. Those lines contain either "Total", "Category", or "Department", but they are mixed into the initial data in random places depending on the initial report. I can design a formula that checks for those unwanted lines and does not copy them, but this leaves blank lines, which is less than optimal. I can design a filter that will eliminate the lines containing the unwanted data, but it has to be run manually, and my users are less than adept with anything on the computer, so this also is less than optimal. In an effort to eliminate the unwanted data altogether, I am looking into trying to make a macro that will filter out those lines that contain any of those keywords. Due to the capabilities of the users, I would like to eventually make the macro run through the use of a button linked to the macro, but I have that part figured out. It's the macro itself that has me stumped.

I copied the macro Pierre-Yves Samyn built and posted here, then did some digging to try to find out how to convert it from CONTAINS to the opposite. I found that I should be able to use DOES_NOT_CONTAIN in place of CONTAINS, so I put that in place, changed the keywords and tried to run it. It does not work. I found documentation on DOES_NOT_CONTAIN on the following page:

It also exists here:

Here is a small sample of the data I am working with:

UF Chocolate Milk .5 gal
UF Intense Chocolate Milk 1 pt
UF Intense Chocolate Milk 1 qt
UF Intense Vanilla Milk 1 pt
UF Milk 1% .5 galu
UF Milk 1% 1 gal
UF Milk 2% .5 gal
UF Milk 2% 1 gal
UF Milk 2% 1 pt
UF Milk 2% 1 qt
UF Milk Homo .5 gal
UF Milk Homo 1 pt
UF Milk Homo 1 qt
UF Milk Homo Gal
UF Milk Skim .5 gal
UF Milk Skim 1 qt
UF Mint Chocolate Chip Milk 1 pt
UF Ult Half & Half 1 qt
Total Category: Dairy
Category: New Age   Type: Retail
UF Orange Juice .5 gal
Total Category: New Age
Total Store: 1
Grand Total


Note that the line "UF Orange Juice .5 gal" falls between several lines that contain the keywords I am trying to filter out.

Here is the macro with the changes I made:

sub PysFilter ...
edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-07-20 11:25:22.971398

Sort by » oldest newest most voted

Hello @ckelley,

The Field index starts at 0, and the line .filterFields2 = oFilterField() is replaced with .setFilterFields2( oFilterField() ).

Sub PysFilter

Dim oSheet As Object, oRange As Object, oFilter As Object
oSheet = thisComponent.Sheets.getByName("Build Up")
oRange = oSheet.getCellRangeByName("A4:A500")
oFilter= oRange.createFilterDescriptor(true)

Dim oFilterField(2) As New com.sun.star.sheet.TableFilterField2
oFilterField(0).Field = 0
oFilterField(0).Operator = com.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(0).StringValue = "Total"

oFilterField(1).Connection = com.sun.star.sheet.FilterConnection.AND
oFilterField(1).Field = 0
oFilterField(1).Operator = com.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(1).StringValue = "Category"

oFilterField(2).Connection = com.sun.star.sheet.FilterConnection.AND
oFilterField(2).Field = 0
oFilterField(2).Operator = com.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(2).StringValue = "Department"

With oFilter
.CopyOutputData = false
.IsCaseSensitive = false
.UseRegularExpressions = false
.setFilterFields2( oFilterField() )
End With

oRange.filter( oFilter )
End Sub

more