[Solved] Macro for Deleting Text in Entire Row

Creating a macro - Writing a Script - Using the API

[Solved] Macro for Deleting Text in Entire Row

Postby daveg7 » Sat Sep 30, 2017 5:12 am

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

Re: Macro for Selecting Text and Numbers in Entire Row

Postby robleyd » Sat Sep 30, 2017 5:45 am

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
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2758
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macro for Selecting Text and Numbers in Entire Row

Postby daveg7 » Sat Sep 30, 2017 6:05 am

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

Re: Macro for Selecting Text and Numbers in Entire Row

Postby FJCC » Sat Sep 30, 2017 6:20 am

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   Expand viewCollapse view
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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7158
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby daveg7 » Sat Sep 30, 2017 7:27 am

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

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

Postby Zizi64 » Sat Sep 30, 2017 9:22 am

[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   Expand viewCollapse view
oRow.clearContents(7)

You need study the passed combined 'cell flag' parameter (7):
https://www.openoffice.org/api/docs/com ... Flags.html
https://forum.openoffice.org/en/forum/v ... f=9&t=2562
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby daveg7 » Sat Sep 30, 2017 9:36 am

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   Expand viewCollapse view
oRow.clearContents(7)

You need study the passed combined 'cell flag' parameter (7):
https://www.openoffice.org/api/docs/com ... Flags.html
https://forum.openoffice.org/en/forum/v ... 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
daveg7
 
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

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

Postby Zizi64 » Sat Sep 30, 2017 11:42 am

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
   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 55 times
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests