I want to fill down a formula next to a filled column.
This could be done with a double click on the fill handle (see screenshot below).
Is there an .uno command that do that?
With the keyboard, I know that I can select the cell range and then press Ctrl+D. But I’d like to bypass the “select the cell range” step.
Thanks.
perhaps
This command (.uno:FillDown) requires that the range be selected beforehand (which I want to avoid). Without selecting the range, it works for one cell/row, being the source the cell/row above.
Maybe what I’m looking for is exclusive for mouse.
Hello, colleagues!
I don’t think this can be done without macros.
Thinking about which part of your answer to quote, I had to laugh.
I suspected it.
Hi @LeroyG, with the cursor at B1, activate the macro…
Sub Preencher
dim doc, disp as object
doc = ThisComponent.CurrentController.Frame
disp = createUnoService("com.sun.star.frame.DispatchHelper")
disp.executeDispatch(doc, ".uno:GoRight", "", 0, Array())
disp.executeDispatch(doc, ".uno:GoDownToEndOfData", "", 0, Array())
disp.executeDispatch(doc, ".uno:GoLeft", "", 0, Array())
disp.executeDispatch(doc, ".uno:GoUpToStartOfDataSel", "", 0, Array())
disp.executeDispatch(doc, ".uno:FillDown", "", 0, Array())
disp.executeDispatch(doc, ".uno:GoUp", "", 0, Array())
disp.executeDispatch(doc, ".uno:GoUpToStartOfData", "", 0, Array())
End Sub 'Preencher
Is there a way to make it work, regardless the filled column is to the right or to the left of the source cell? In other words: What if the original data is in column A or in column C, or in both columns?
Sub fillDown_in_CurrentRegion()
'calls getCurrentRegion, getRangeByAddress
sel = ThisComponent.getCurrentSelection()
if not sel.supportsService("com.sun.star.sheet.SheetCell") then exit sub
adr = sel.getRangeAddress()
cr = getCurrentRegion(sel)
adr.EndRow = cr.RangeAddress.EndRow
rg = getRangeByAddress(ThisComponent, adr)
rg.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM,com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
End Sub
Helper functions:
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
REM use the sheet specified by given address
oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
else
REM use given object (range/sheet) as parent range
oSheet = obj
endif
getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
getRangeByAddress = Null
End Function
Function getCurrentRegion(oRange)
Dim oCursor
oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
oCursor.collapseToCurrentRegion
getCurrentRegion = oCursor
End Function