Ask Your Question

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

asked 2017-02-22 19:53:02 +0100

ckelley gravatar image

updated 2020-07-20 11:25:11 +0100

Alex Kemp gravatar image

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted

answered 2017-08-28 10:53:19 +0100

librebel gravatar image

Hello @ckelley,

To make your macro work, it just takes a few adjustments:

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
    oFilterField(0).Field = 0
    oFilterField(0).Operator =
    oFilterField(0).StringValue = "Total"

    oFilterField(1).Connection =
    oFilterField(1).Field = 0
    oFilterField(1).Operator =
    oFilterField(1).StringValue = "Category"

    oFilterField(2).Connection =
    oFilterField(2).Field = 0
    oFilterField(2).Operator =
    oFilterField(2).StringValue = "Department"

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

    oRange.filter( oFilter )
End Sub
edit flag offensive delete link more


This works exactly the way I wanted it to, thank you!

ckelley gravatar imageckelley ( 2017-09-25 21:28:23 +0100 )edit

Question Tools

1 follower


Asked: 2017-02-22 19:53:02 +0100

Seen: 118 times

Last updated: Aug 28 '17