Calc: Autofit the height of the merged cell with a macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Calc: Autofit the height of the merged cell with a macro

Post by eeigor »

There are very long names, and the column width is limited.
The situation with the "unpopular" data storage on the sheet is the merging of cells. Yes, this should be avoided.
Such data cannot be further processed. But sometimes you don't have to. We store it as we print it.
We need to make data storage compact and that's it.

The problem is that after merging the cells, such a merged cell itself does not expand the height, and this must be done manually.
You need a macro that, after editing the data in the cell, will do something like this:
- cancel the merging of cells (the data will end up in the top cell);
- auto-adjust the height of the row of the data cell and remembers the resulting height;
- merge the cells back together and... do something so that the row height of the merged cells in the sum is equal to the previously saved value (how to divide the heights of the rows that make up the combined row is an open question: equally or otherwise).

Perhaps there is a ready-made solution...
With combined columns, there is no need to do such manipulations (combining and editing data in columns is a rarer case).
If there is such a macro for LO Calc, please share it...

UPD: VBA
Attachments
Снимок экрана от 2021-03-26 21-14-57.png
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc: Autofit the height of the merged cell with a macro

Post by Zizi64 »

Please upload your real, ODF type sample file here.

UPD: VBA
The VBA is not compatible with AOO/LO. Use the Starbasic (or other supported programming language), AND the API functions.

API: Application Programming Interface.
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.
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Autofit the height of the merged cell with a macro

Post by eeigor »

@Zizi64, I am not using VBA. The link points to the VBA example I'm looking for off the shelf for LO Calc. I came from there to here and before reinventing the wheel, I try to look for an analogue...
In the screenshot above, the merged cell displays the text partially. It is necessary that after editing the height is set automatically, as for non-merged cells.
No example needed. Merge at least 2 cells of two adjacent rows and start typing long text... Column width is fixed. If several cells are also merged by columns, then the code will be more complicated.

Of course, I can write this code myself (not so fast), but I don't have enough time to rewrite everything (written in VBA). With the help of the community, it turns out faster, and something stays here... The tasks I raise are basically trivial, and someone has already solved them.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc: Autofit the height of the merged cell with a macro

Post by Zizi64 »

No example needed.
Sorry, I can not decide based on the image, what you want to achieve, and if you use a Cell Style with "Wrap text automatically" property, or you wrap the text manually...

And what is the excepted state in the case of the three wrapped line? The adjacent TWO cells/rows never will be THREE cells/rows.


(My hint: Use merged cells in the next column too with combined cell content...)
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.
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Autofit the height of the merged cell with a macro

Post by eeigor »

Autowrap, of course. And AutoFit is needed.
But nested strings can be divided equally (with values aligned ​​vertically to the top), I don't know... Or resize only the first row.

UPD: I just got closer to my goal, but I have to put off work. If anyone is interested in this task, then I suggest taking part.
However, when designing a procedure, things can get much more complicated.
Local units (cm)
Attachments
Снимок экрана от 2021-03-27 21-00-54.png
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Post Reply