[Solved] Macro for Deleting Text in Entire Row
[Solved] Macro for Deleting Text in Entire Row
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.
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.
Last edited by daveg7 on Sat Sep 30, 2017 9:38 am, edited 3 times in total.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
Re: Macro for Selecting Text and Numbers in Entire Row
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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: Macro for Selecting Text and Numbers in Entire Row
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?
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?
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
Re: Macro for Selecting Text and Numbers in Entire Row
Here is a very simple version with no error checking. For example, it does not check that the current selection is a cell.
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.
Code: Select all
oCurSel = ThisComponent.CurrentSelection
RangeAddress = oCurSel.RangeAddress
RowIdx = RangeAddress.StartRow
oSheet = ThisComponent.CurrentController.ActiveSheet
oRow = oSheet.getRows().getByIndex(RowIdx)
oRow.clearContents(7)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
[Solved] Macro for Selecting Text and Numbers in Entire Row
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: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.
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
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
Re: [Solved, Imperfectly] Macro for Deleting Text in Entire
I my opinion, FJCC's solution is perfect.[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.
Code: Select all
oRow.clearContents(7)
https://www.openoffice.org/api/docs/com ... Flags.html
viewtopic.php?f=9&t=2562
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved, Imperfectly] Macro for Deleting Text in Entire
Zizi64 wrote:I my opinion, FJCC's solution is perfect.[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.
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):Code: Select all
oRow.clearContents(7)
https://www.openoffice.org/api/docs/com ... Flags.html
viewtopic.php?f=9&t=2562
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
Re: [Solved] Macro for Deleting Text in Entire Row
Let see the code line by line:Code: Select all
oCurSel = ThisComponent.CurrentSelection RangeAddress = oCurSel.RangeAddress RowIdx = RangeAddress.StartRow oSheet = ThisComponent.CurrentController.ActiveSheet oRow = oSheet.getRows().getByIndex(RowIdx) oRow.clearContents(7)
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."Code: Select all
oCurSel = ThisComponent.CurrentSelection
You can examine it with the one of the methodes of the object (Use an object inspection Tool!):
Code: Select all
Xray oCurSel
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
There are some more comments in the sample file:
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.