Would like to write macro to delete rows based on criteria

[macevhicz]

Hello!

I'm trying to write a short macro for a Calc file to go through many rows, and delete individual rows if certain strings are found in a particular column for the given row. Alternatively, I would like the macro to first copy the contents of said row into a separate sheet, so I'm not actually losing the data in that row, just transferring it to a different sheet.

It's something I could do in VBA for Excel, though I'm having an extremely hard time finding out what methods & properties are available to manipulate rows, or to copy/paste rows.

I've found various webpages, but none are providing the info I need. Some, like this one, are total gibberish to me.
http://www.openoffice.org/api/docs/comm ... elper.html

I've seen that "com.sun.star..." stuff a few times, and have no idea what it means, and searching l'internet hasn't helped.

Is there a place that lists all the OpenOffice Calc methods and/or properties?

Any help would be greatly appreciated!

[Villeroy]

A forum can not teach macro programming to each and every new user.

1. Apply your (auto-) filter
2. Select the list below the header row using the regular well known shortcuts
3. Delete entire rows (I bound Ctrl+Del to "Delete Rows")

A recorded macro does not give you any clue about macro programming. Recorded macros can call one particular method which dispatches actions to the user interface (select this, call that command). This has nothing to do with macro programming. Recorded macros are virtually useless.
If you think that spreadsheets should be operated through macros, then there will never be any alternative for Excel/VBA.

[Ruperto]

If you wanting to get to grips with OpenOffice macros there is always going to be a learning curve. Do not try and jump straight into writing the macro. Do some reading and practice writing some simple macros first.

Some reading:

"Porting Excel/VBA to Calc/StarBasic":

http://www.openoffice.org/documentation ... icXref.pdf

The OpenOffice guide:

http://wiki.openoffice.org/wiki/Documen ... readsheets

"OpenOffice.org Macros Explained" by Andrew Pitonyak:

Google to download.

Try and write some simple macros eg:

That accesses a workbook/sheet/cell
That retrieves a string from a cell
That copies and pastes a cell or range of cells.
That deletes a row

This will give you confidence and some building blocks.

Looking at the macro you eventually want to write, I guess you will need a "Do..Loop" which is identical or almost identical to how it is implemented in VBA.

Also you should make sure you understand the OpenOffice IDE and how / where macros are stored. This is quite different from Excel.

Once you know the above, you should be well on the way to writing the macro you want!

[Villeroy]

Even the stupid macro recorder can record complex actions if you know how to navigate a spreadsheet. But knowing how to navigate a spreadsheet with a few keystrokes, one does not need such macros anymore.

Code: Select all

rem ----------------------------------------------------------------------
REM Ctrl+Home key (goto top-left cell)
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Sel"
args3(0).Value = false

dispatcher.executeDispatch(document, ".uno:GoToStart", "", 0, args3())

rem ----------------------------------------------------------------------
REM down arrow (go one row down below the header row)
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args4())

rem ----------------------------------------------------------------------
REM Ctrl+Shift+End (expand selection until end of data)
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Sel"
args5(0).Value = true

dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args5())

rem ----------------------------------------------------------------------
REM Shift+Space (select entire rows)
dispatcher.executeDispatch(document, ".uno:SelectRow", "", 0, Array())

rem ----------------------------------------------------------------------
REM Ctrl - (delete cells)
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())

REM remove filter
dispatcher.executeDispatch(document, ".uno:DataFilterRemoveFilter", "", 0, Array())

If the rows had been filtered by certain criteria (which can not be recorded but predefined by a so called "advanced filter"), this dispatch sequence deletes the visible rows, excluding the first header row. Then it removes the filter.

[macevhicz]

Thank you both for you quick replies! They were quicker than I could use them, as I've been away from the computer since Friday.

Yes, I realize the forum cannot teach new users everything... I was basically looking for some documentation that details available methods, as I do not see any other way to discover them other than recording a macro and reading what it recorded, which (I agree) teaches very little.

In my attempts to do so, I recorded a macro in which I selected a row ( by clicking the row number), then deleted the row. When examining the code that was saved in the macro, there was nothing to indicate row selection, which I could then modify to iterate through the whole list. Here is the entirety of what was saved...

dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())

How that macro would ever know what row to delete is beyond me, aside from what ever row was already active. But even when I recorded selecting then deleting multiple rows in succession, it never showed anything about selecting a row, or making a row active.

I have tried the manual method of creating a filter (advanced, as the auto-filter seems to go by entire cell contents, not strings within cells contents). After filtering, and deleting those rows, it behaved strangely. I originally had about 1000 rows, and via filtering, I deleted about 150 rows. At this point, I could not scroll any higher than row 850. It behaved as if rows 1-849 no longer existed. I imagine it was still filtered, although there was no longer an applied filter which could be turned off.

I will try to look through that porting vba to starbasic doc to see if that offers any insight...

[Ruperto]

I forgot to mention..I would not rely on the macro recorder in OpenOffice to write macros. It is not the same as the Excel macro recorder which produces the code and then all you have to do is some tweaking. In OpenOffice I usually find it best to write macros from scratch. This means first doing some reading and practice writing some simple macros. However, it is worth it in the long run.

[Phos4us]

DeleteApples.ods

Automated row deletion

(13.62 KiB) Downloaded 436 times

Hi macevhicz

Here is some code you might find of interest:

Code: Select all

Sub DeleteApples
    oSheet=thiscomponent.getcurrentcontroller.activesheet
    ' This routine assumes a single column range contains the values to be checked for row deletion
    oRows = ThisComponent.CurrentController.ActiveSheet.Rows
    nCol = oRange.getRangeAddress.StartColumn
    for nRow = nStartRow to nEndRow
        ' Next line specifies a value qualifying the row for deletion
        if oCell.GetString = "apples" then 
                   Count = Count + 1
           oRows.removeByIndex(nRow,1) 'the second argument limits deletion to 1 row at a time
    next
End Sub

See attachment for demo.

[Phos4us]

DeleteApples.ods

(13.51 KiB) Downloaded 337 times

My code listing shown above is missing some lines which I now find I had indented with tabs.
I have removed the indenting (whether with tabs or spaces) and resubmitted it below.
What I cannot explain is why my submitted file when I download it produces a runtime error at "oRows.removeByIndex(nRow,1)" when the identical original runs without a problem.
Anyway here is my second listing, hopefully complete this time:

Code: Select all

Sub DeleteApples
oSheet=thiscomponent.getcurrentcontroller.activesheet
rem This routine assumes a single column range contains the values to be checked for row deletion
oRange = oSheet.GetCellRangeByName("F11:F18") 
oRows = ThisComponent.CurrentController.ActiveSheet.Rows
nStartRow = oRange.getRangeAddress.StartRow
nEndRow = oRange.getRangeAddress.EndRow
nCol = oRange.getRangeAddress.StartColumn
for nRow = nStartRow to nEndRow
rem The next line specifies the cell to be checked
oCell = oSheet.GetCellByPosition(nCol,nRow) 
rem The next line specifies a value qualifying the row for deletion
if oCell.GetString = "apples" then 
Count = Count + 1
rem The second argument in the next line limits deletion to 1 row at a time
oRows.removeByIndex(nRow,1)
End if
next
MsgBox Count & " rows deleted"
End Sub

[Villeroy]

The above macro fails.
Put "apples" into the whole range F11:F18. Only half of the cells get deleted because the loop fetches the row nRow+1 which used to be nRow+2 before the deletion.

Code: Select all

for nRow = nEndRow to nStartRow step -1

fixes that problem.

Again: Spreadsheets are made for non-programmers so they do not have to write bad code to solve trivial problems.

[Phos4us]

Thanks Villeroy>

I'll make sure I'm more rigorous with my testing before submitting any more code!