In MS Excel, if you select the whole spreadsheet and then hover over the column separator so you see the double-headed arrow and double-click the column separator line it will adjust all of the column widths to match the longest content for that column. Is there an equivalent function in Calc?
2 Likes
Hi! Is there a way to set the width and height at once?
Option Explicit
'For use, place =SETOPTIMALWIDTH(NOW()) in the spreadsheet, in any cell.
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
Works the same in Calc. Alternatively right click on a column header and from context menu choose Optimal Width.
Strange, for whatever reason it does not perform it when simply double clicking the divider bar, but it does if I use the right-click and choose Optimal Width method. Anyway, I was able to get the desired result, so thank you!
@erAck, does that allow it to continuously automatically resize, thereafter? I ask because I’m surprised that it’s not the default, and appears tedious to accomplish: