[Solved] Macro for Deleting Text in Entire Row

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
daveg7
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

[Solved] Macro for Deleting Text in Entire Row

Post 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.
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
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macro for Selecting Text and Numbers in Entire Row

Post 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.
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
daveg7
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: Macro for Selecting Text and Numbers in Entire Row

Post 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?
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro for Selecting Text and Numbers in Entire Row

Post 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.
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.
daveg7
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

[Solved] Macro for Selecting Text and Numbers in Entire Row

Post 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
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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved, Imperfectly] Macro for Deleting Text in Entire

Post 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.

Code: Select all

oRow.clearContents(7)
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
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.
daveg7
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: [Solved, Imperfectly] Macro for Deleting Text in Entire

Post 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. ;)

Code: Select all

oRow.clearContents(7)
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
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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Macro for Deleting Text in Entire Row

Post 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!):

Code: Select all

Xray oCurSel 
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:
Delete_contents.ods
(19.05 KiB) Downloaded 167 times
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.
Post Reply