Page 1 of 1
[Solved] Macro for Deleting Text in Entire Row
Posted: Sat Sep 30, 2017 5:12 am
by daveg7
I'm trying to write a macro that will select the entire current row, then delete all content, except for formulas and styles.
Here's what I have so far for what I hope will do the deleting, but I don't know how to do the selection:
Sub clearTextButNotFormulas
Thiscomponent.CurrentSelection.ClearContents(4)
End Sub
Thanks, in advance.
Re: Macro for Selecting Text and Numbers in Entire Row
Posted: Sat Sep 30, 2017 5:45 am
by robleyd
Try recording a macro that just selects an entire, empty row and see if examining that macro gives you a push in the right direction.
Re: Macro for Selecting Text and Numbers in Entire Row
Posted: Sat Sep 30, 2017 6:05 am
by daveg7
Thanks. I just tried your suggestion.
However, using the recorder on the Mac doesn't give me the Basic code that I'm looking for. Instead I get some gibberish that I'd have to spend a long time learning a lower-level language to understand. I'm surprised that the Mac has made this so inconvenient, or is it OpenOffice?
Re: Macro for Selecting Text and Numbers in Entire Row
Posted: Sat Sep 30, 2017 6:20 am
by FJCC
Here is a very simple version with no error checking. For example, it does not check that the current selection is a cell.
Code: Select all
oCurSel = ThisComponent.CurrentSelection
RangeAddress = oCurSel.RangeAddress
RowIdx = RangeAddress.StartRow
oSheet = ThisComponent.CurrentController.ActiveSheet
oRow = oSheet.getRows().getByIndex(RowIdx)
oRow.clearContents(7)
The value 7 passed to clearContents selects for constant numeric values that are not formatted as dates or times, dates and times, and constant strings.
[Solved] Macro for Selecting Text and Numbers in Entire Row
Posted: Sat Sep 30, 2017 7:27 am
by daveg7
robleyd wrote:Try recording a macro that just selects an entire, empty row and see if examining that macro gives you a push in the right direction.
Well, thanks again. I finally found two undocumented key combinations (at
https://ask.libreoffice.org/en/question ... -shortcut/) that helped me to select an entire row or entire column:
To select the whole line irrespective of where you are on that line use: Shift+Space
To select the whole column irrespective of where you are in that column us: Ctrl+Shift+Space
Since the simple recording of the keystrokes for selecting the entire row and using the DEL key for the content (except, as I needed, for styles and formulas) worked, I'll just accept using the recording for my project.
However, there are two caveats:
1) All the cells have to be unprotected
2) Even after I unprotected the sheet, I had to unhide all the columns to make the macro work
So the solution works for me (a little clunky), and I'll mark it solved.
Ideally, however, I'd be able to keep all those columns hidden. If I could find a way to select only the unhidden columns, I think that should do it.
Thanks again.
David
Re: [Solved, Imperfectly] Macro for Deleting Text in Entire
Posted: Sat Sep 30, 2017 9:22 am
by Zizi64
[Solved, Imperfectly]
I'm trying to write a macro that will select the entire current row, then delete all content, except for formulas and styles.
I my opinion, FJCC's solution is perfect.
You need study the passed combined 'cell flag' parameter (7):
https://www.openoffice.org/api/docs/com ... Flags.html
viewtopic.php?f=9&t=2562
Re: [Solved, Imperfectly] Macro for Deleting Text in Entire
Posted: Sat Sep 30, 2017 9:36 am
by daveg7
Zizi64 wrote:[Solved, Imperfectly]
I'm trying to write a macro that will select the entire current row, then delete all content, except for formulas and styles.
I my opinion, FJCC's solution is perfect.
Your opinion weighs more heavily on my scale than my ignorance! 
I'll certainly experiment with FJCC's solution, even if I don't yet understand the code.
You need study the passed combined 'cell flag' parameter (7):
https://www.openoffice.org/api/docs/com ... Flags.html
viewtopic.php?f=9&t=2562
Re: [Solved] Macro for Deleting Text in Entire Row
Posted: Sat Sep 30, 2017 11:42 am
by Zizi64
Code: Select all
oCurSel = ThisComponent.CurrentSelection
RangeAddress = oCurSel.RangeAddress
RowIdx = RangeAddress.StartRow
oSheet = ThisComponent.CurrentController.ActiveSheet
oRow = oSheet.getRows().getByIndex(RowIdx)
oRow.clearContents(7)
Let see the code line by line:
Code: Select all
oCurSel = ThisComponent.CurrentSelection
oCurSel is an object type variable, and the code line try to get the objects of the current selection. Maybe the current selection is a cell, or a cellrange, but maybe it is not. FJCC noted: "Here is a very simple version with no error checking. For example, it does not check that the current selection is a cell."
You can examine it with the one of the methodes of the object (Use an object inspection Tool!):
Search the ImplementationName propery on the Xray panel. You will see the implementation name of the actual selected Object/s/
The you can examine the property value:
Code: Select all
If (oCurSel.Implementationname<>"ScCellObj" and oCurSel.Implementationname<>"ScCellRangeObj") then
Msgbox("The current selection is not a cell or a cellrange!")
Exit sub
End if
Then you can delete the line of the Xray command/s/ from your code.
There are some more comments in the sample file: