It’s fine if I ctrl+c ctrl+v the borders or just use the manual border format, but it’s annoying that borders get changed every time I need to drag copy updated formula which either clears some borders or creates staggered ugly borders.
Use the Paste Special feature.
And use the Cell Styles instead of the manual (direct) formatting method.
Hallo
Try ctrl+shift+v
Assuming this is specifically about filling ranges by dragging with the mouse.
No. The relevant reason may be that the extensions to the UI needed to allow for all the options and settings concering restrictable fill commands are too complicated. After all cell borders aren’t the only table formats one user may want to copy, but another user may not. Even if I interpret the request as “don’t fill down any formatting”, there is no exactly simple solution.
I considered a solution by user code+shortcut, but even going this way there are problems due to lacking support by the API. I needed to resort to a recorded and then reworked macro, this way learning what the dispatcher has to offer.
Thus my actually serious suggestion is: Reduce the setting of attributes to an absolute minimum. In fact they are mostly much less useful as many users seem to think, and often they even are distracting and disturbing.
Not quite as serious, but actually applicable is the solution by custom code I created in a playful mood. An example is attached:
ask91235dragFillDownNoFormats.ods (24.7 KB)
While this still doesn’t work directly with the autofill pull down crosshair, I think this is as much of an answer as @Lupp’s. It’s also based on quite a bit of Lupp’s work.
To use this, highlight the one cell you want to use as the seed and the rest of the cells you want to fill into, so that together they become the Calc selection. Then activate the macro (say, by assigning it to a key via the Customize… menu).
Sub ExampleFillRangeNB()
Rem With thanks to Lupp on borderlines, tableborders and exit condition
Dim Selection As Object
Selection = ThisComponent.CurrentSelection
If Not Selection.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub
Rem com.sun.star.sheet.FillMode.SIMPLE just copies values
Rem com.sun.star.sheet.FillMode.LINEAR predicts values
FillRangeNoBorders(Selection, com.sun.star.sheet.FillMode.SIMPLE)
End Sub
Sub FillRangeNoBorders(InputRange As Object, FillMode As Variant)
Dim StartColumn As Integer
Dim EndColumn As Integer
Dim StartRow As Integer
Dim EndRow As Integer
Dim Sheet As Object
Dim Range As Object
Dim BorderLine As New com.sun.star.table.BorderLine2
Dim TableBorder As New com.sun.star.table.TableBorder2
Sheet = InputRange.SpreadSheet
StartColumn = InputRange.getRangeAddress.startColumn
EndColumn = InputRange.getRangeAddress.endColumn
StartRow = InputRange.getRangeAddress.startRow
EndRow = InputRange.getRangeAddress.endRow
Range = Sheet.getCellRangeByPosition(StartColumn, StartRow, EndColumn, EndRow)
Range.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM, FillMode, com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 1, 100)
Range = Sheet.getCellRangeByPosition(StartColumn, StartRow + 1, EndColumn, EndRow)
BorderLine.LineStyle = com.sun.star.table.BorderLineStyle.NONE
TableBorder.VerticalLine = BorderLine
TableBorder.IsVerticalLineValid = True
TableBorder.HorizontalLine = BorderLine
TableBorder.IsHorizontalLineValid = True
TableBorder.TopLine = BorderLine
TableBorder.IsTopLineValid = True
TableBorder.BottomLine = BorderLine
TableBorder.IsBottomLineValid = True
TableBorder.LeftLine = BorderLine
TableBorder.IsLeftLineValid = True
TableBorder.RightLine = BorderLine
TableBorder.IsRightLineValid = True
Range.TableBorder2 = TableBorder
End Sub
Check [LibreOffice: XCellSeries Interface Reference] to see how to use .fillAuto instead of .fillSeries if you need more than one seed cell. Then adjust the StartRow + n as needed, or put that as a variable with the nSourceCount .fillAuto parameter, etc.