How to copy the values or cells from a matrix subrange in Calc?

[Sancho1]

In Calc, version 5.x, if you want to copy only a subrange of cells that are the result of a matrix formula, how to do that?

That is, for example, if you put into cells A1:A5 respectively the values 1 to 5, and to fill B1:B5 you put into B1 the matrix formula {=A1:A5+1} (ctrl+shift+enter). Then, you want to copy only the cells or values B2:B4, but, selecting B2:B4 the copy option isn’t enabled, why? how to copy a subrange only of a matrix calculation?

Is properly a problem or design of Calc version 5.x? or is a configuration matter? Is there any way to do that selected subrange copy?

Thanks!

[JohnSUN]

You can use the macro proposed by the distinguished colleague Lupp or you can write in an empty free cell =B2, stretch this formula to the desired number of cells and copy or cut the result from there

[Sancho1]

Thanks. This is a very good way. As you stated, a solution is to put the array/matrix formula inside another sheet named “calculations” for example, inside the original sheet used to show the values put the references to the entire range wanted from the array/matrix calculations, and then, on this original sheet, be able to copy and paste a selected subrange of the calculations as usual. It works. Thanks!

[Lupp]

Yes, that’s a good concept. And even if no array-formulae are needed it can be advantageous to externalise calculations to a different sheet. As often with spreadsheets you cannot decide what’s the best way without knowing numbers. Referencing many thousands of results of calculations done elsewhere requires a lot of formulae, and may bloat the file and cut down efficiency. …
You can well sort a few dozen values using LARGE(), and completely fail with 10000, e.g.

[Lupp]

A bit of philosophy:
Having selected any CellRange of a sheet, the copy command is used to copy “everything” connected to that range. This always includes the formula / formulae if any. In the same sense as a true subrange of the locked output range of an array-formula cannot be edited, it cannot be copied therefor.

To allow for the ‘Copy’ command you actually want - without the formulae - a command like ‘Copy Special…’ in analogy to ‘Paste Special…’ would be needed.

You may file a feature request (“enhancement”) to https://bugs.documentfoundation.org/. Feature requests are treated exactly like (as) bugs.

If you don’t shy back from resorting to custom code: It isn’t exactly difficult to get the needed commands this way.

Edit1 adding provisional code and attaching this demo.

REM  *****  BASIC  *****
REM 2018-04-06 Wolfgang Jäger

Global copiedDA As Object

Sub copyDA()
REM 2018-04-06 Wolfgang Jäger
doc0 = ThisComponent
theSel = doc0.CurrentSelection
If NOT theSel.SupportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub 
copiedDA = theSel.GetDataArray
End Sub

Sub pasteDA()
REM 2018-04-06 Wolfgang Jäger
REM The target range is exclusively based on the cell having currently the focus
REM even if this cell isn't currently selected itself. The selection as shown
REM by highlighting is ignored. The dimensions of the target range are taken
REM from the previously taken DataArray. 
REM Formats are ignored. Trying to do it otherwise would raise serious complications, imo:
REM first copy using the sheet method CopyRange, then ClearContents, then as shown here.
doc0 = ThisComponent
If NOT doc0.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub
fC = focusCell(doc0.CurrentController)
On Error Goto errorExit
m = Ubound(copiedDA(0)) : n = Ubound(copiedDA)
With fC.CellAddress
  target = fC.Spreadsheet.GetCellRangeByPosition(.Column, .Row, .Column + m, .Row + n)
End With
target.SetDataArray(copiedDA)
errorExit:
End Sub

REM === The following helper function is the most obscure part of the solution. ===
Function focusCell(Optional pCtrl) As Object
REM Concept by "uros", "Villeroy"
REM Responsible for this variant: Wolfgang Jäger
REM 2017-09-28 V0
If IsMissing(pCtrl) Then pCtrl = ThisComponent.CurrentController
If  NOT pCtrl.SupportsService("com.sun.star.sheet.SpreadsheetView") Then Exit Function
Dim theSheet As Object, fC As Object, sheetNum As Long, sInfo As String
Dim sInfoDelim As String, vD, vDSplit, sInfoSplit
vD             = pCtrl.ViewData
vDSplit        = Split(vD, ";")
theSheet       = pCtrl.ActiveSheet
sheetNum       = theSheet.RangeAddress.Sheet
sInfo          = vDSplit(sheetNum + 3)
REM For CellAddress.Row >= 8192 the "+" is used as the subdelimiter in ViewData. WHY?
If InStr(sInfo, "+")>0 Then 
  sInfoDelim = "+"
Else
  sInfoDelim = "/"
End If
sInfoSplit     = Split(sInfo, sInfoDelim)
fC             = theSheet.GetCellByPosition(sInfoSplit(0), sInfoSplit(1))
focusCell      = fC
End Function  

Edit2 (Obsolete line of code deleted; delete also the respective comment.)
When I tested the macros I noticed that the action by the Sub ‘pasteDA’ is not always cleanly made undone using the respective command. Cells that were blank previously retain the inserted content wrongly after Ctrl+Z.
I therefor posted the bug report tdf#116850.

[Sancho1]

I think the way you stated here point to be a very good enhancement of the copy/paste commands, having from factory the function to copy and paste a subrange of values comming from an array/matrix formula, without turnarounds, despite the need to be an extra function for the insight you gave. Thanks!