Would like to write macro to delete rows based on criteria
Would like to write macro to delete rows based on criteria
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!
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!
OpenOffice 3.4.1 on Mac OSX 10.7.5
Re: Would like to write macro to delete rows based on criter
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Would like to write macro to delete rows based on criter
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!
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!
OpenOffice 3.4.1
Windows 7
Windows 7
Re: Would like to write macro to delete rows based on criter
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.
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.
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())
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Would like to write macro to delete rows based on criter
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...
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...
OpenOffice 3.4.1 on Mac OSX 10.7.5
Re: Would like to write macro to delete rows based on criter
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.
OpenOffice 3.4.1
Windows 7
Windows 7
Re: Would like to write macro to delete rows based on criter
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
Open Office 3.6.4.3 on Windows Vista
Re: Would like to write macro to delete rows based on criter
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
Last edited by RoryOF on Mon Aug 12, 2013 6:35 pm, edited 1 time in total.
Reason: Added code tags (RoryOF, Moderator)
Reason: Added code tags (RoryOF, Moderator)
Open Office 3.6.4.3 on Windows Vista
Re: Would like to write macro to delete rows based on criter
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.
fixes that problem.
Again: Spreadsheets are made for non-programmers so they do not have to write bad code to solve trivial problems.
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
Again: Spreadsheets are made for non-programmers so they do not have to write bad code to solve trivial problems.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Would like to write macro to delete rows based on criter
Thanks Villeroy>
I'll make sure I'm more rigorous with my testing before submitting any more code!
I'll make sure I'm more rigorous with my testing before submitting any more code!
Open Office 3.6.4.3 on Windows Vista