Hi everyone,
I need to Click or do some steps to “Optimal Width” each time i want, and it so annoying, wasted my time.
Do you have any ideas ?
Thanks in advance.
===============================
Maybe answered : Create shortcut keys or use “alt+o+m+o” for this feature, because there is no function in libreoffice to autofit column.
Please tell me if you found something more helpful. Thank you.
I largely agree with my respected colleague @Lupp and recommend that you heed his advice. I don’t believe that hip-hop will be permanent. However, I warn you about another danger: if the text in the cell is too long, then the automatic width can make the column too wide.
indeed, the solution to your problem is to use a macro. For example, like this:
Option Explicit
Function SetOptimalWidth(Optional oDummy As Variant) As String
Const MAX_WIDTH = 10000
Dim oCurrentController As Variant
Dim oActiveSheet As Variant
Dim oCursor As Variant
Dim oColumns As Variant
Dim oColumn As Variant
Dim i As Long
Rem First of all, we find out the active sheet
oCurrentController = ThisComponent.getCurrentController()
oActiveSheet = oCurrentController.getActiveSheet()
Rem In order not to change all the columns on the sheet, we find the UsedRange
oCursor = oActiveSheet.createCursor()
oCursor.gotoEndOfUsedArea(True)
oColumns = oCursor.getColumns()
Rem Now we optimize the width of each column in a given range
For i = oColumns.getCount() - 1 To 0 Step -1
oColumn = oColumns.getByIndex(i)
oColumn.OptimalWidth = True
Rem We could stop at this
Rem But some columns may become so wide that they won’t fit on the screen
Rem In this example, we will limit the maximum width to 10 cm
If oColumn.Width > MAX_WIDTH Then
oColumn.OptimalWidth = False
oColumn.Width = MAX_WIDTH
EndIf
Next i
SetOptimalWidth = "Clear this cell if you want to stop the automatic column width"
End Function
Put this code in the Standard library in My Macros & Dialogs
Now put the formula =SETOPTIMALWIDTH(NOW()) in any cell of the sheet.
Using the NOW() function as parameter will cause the macro to run after each change on the current sheet. Moreover, this macro will also be executed if you press F9 (or choose Data - Calculate - Recalculate) on any other sheet of the spreadsheet.
This simple trick allows you not to use explicit sheet events that a colleague suggests exploring. The formula is very simple to insert into any cell and is just as easy to remove when the need for this automation disappears
Update In order not to use complex solutions using macros, assign a convenient hotkey to the “Optimal Column Width, direct” command and use it (Choose Tools - Customize - Keyboard tab)
Thank you and @Lupp for helped me. I just want to sure there are no funtions to autofit column in Libreoffice like Excel, and with 2 answered from you and @Lupp, I think i will use the shortcut keys “Alt + O + M + O” to solve my problem, Because i don’t really want to do that by “macro” or some complex things.
The keyboard shortcuts you described can be much shorter. I supplemented my answer.
Thank you so much for helped me and show me options to solve this problem.
Hello,
While it’s true that text in cells can sometimes lead to overly wide columns, I believe there’s a solution that can address this issue without sacrificing convenience.
By implementing a maximum width limit for auto-fitting column widths, we can ensure that columns are optimized for readability while avoiding excessive expansion. This approach allows most columns to benefit from optimized widths, while longer text is automatically wrapped within the specified limit.
This balance between usability and readability can greatly improve the user experience without compromising on practicality.
I would appreciate your consideration of this proposal as a potential solution to the current issue.
Thank you for your attention to this matter.
Yes, this is not difficult to achieve if after the line
oColumn.Width = MAX_WIDTH
you add another line of code
oColumn.IsTextWrapped = True
But here another danger awaits us - in the case of very long text, the row height may be greater than the height of the screen and the text will again become unreadable (part of the text will disappear behind the bottom border of the window).
Oops, my bad! I meant to say truncated, not wrapped. So, what if the text were cut off instead of going to the next line?
Sorry, I don’t quite understand your idea. “Truncated” - do you mean “truncated on the right”, hide the end of a long string? But this is exactly what will happen automatically if TextWrapped is not enabled for the cell. Or are you asking about the possibility of cutting out the middle of a long string of text and replacing it with, for example, an ellipsis?
Do you actually want to often adjust all the columns of a sheet to so-called OptimalWidth?
If I remeber correctly there once was a feature request to that effect. I would’t want to have columns dancing hip-hop off and back to the view due to automatic adaption of column widths.
Actually all columns are set by default to .OptimalWidth=true. However - in accordance with my above statement - the property only is applied a next time if this setting explicily is renewed.
I would prefer a selective behaviour: Some specific cells are formatted in a way causing the wdith of their columns to be adapted if their direct content (excluding formula results) was changed.
In addition (and seldom used) I may then also have the button you were talking of - or a sensitive area in a personal toolbar forcing optimal width adaption for all columns (which will set blank columns to the default width).
Neither proceeding is supported by an existing tool directly. The second variant, however is needing very few steps to be done by the user:
Click the top left corner of the label ranges or hit Ctrl+A (default) to SelectAll.
Right click an arbitrary one of the column labels.
Choose Optimal Width... from the context menu.
The “button-solution” or any way of an enhanced automated proceeding will require user code, afaik.
The attached example document demonstrates how it might be done.
In place of buttons I created a special toolbar saved to the document an inserted ‘floating’. That’s the better way in such a case, imo.
(You will need to study the Basic code to understand the details concerning the usage.
This was kind of fun to me [as I see no use-case in my personal world] and fun would be spoilt if I wrote a manual.
Just one hint: The cell styles containing “ColAutoWidth” in their names are in possession of the wand.
And the respective Sub must be assigned to the ‘Content Changed’ event of the sheet.
The more general commands are not restricted - or in a different way, depending on your settings.)
The example now:
ask236486autoOptimalColumnWidth_1.ods
I don’t claim the contained code to be “matured software”. It might be bettr matured than some “software” I met in the wild.
