I am trying to copy a row with 32 non-merged cells into another row with 32 cells, each of which takes up 4 columns. If I just select the first cell of the destination row and hit ‘paste only text’, some of the items are pasted into the hidden cells and are not shown. If I select all 32 cells of the destination row and paste, the cells are filled, but not in the order of the original row.
I could paste them in one by one, but there has to be a better way.
@Voluminous: Please consider that “merging” of cells might be a feature that was implemented without thorough analysis of the consequences in every application case - and most likely without a sufficiently complete specification in advance. Thus: If you decide to file an RFE consider expectable consequences yourself and provide a specification of the changes/enhancements you suggest.
General assumption: Merging (except for headings, probably) is evil. No pasting into merged areas!
Quoting @Voluminous: “If I select all 32 cells of the destination row and paste,…”
How did you do the selection? The cells were merged after all. Did you use the Name Box?
Anyway. Pasting with Ctrl+V (or equivalent) into the merged area resulted as expected in split cells with the correct content. Merging is handled as a direct format.
Would you, please, describe your proceeding more precisely so I can reproduce it?
The original row selection looks like “B1:AG1” The destination selection looks like “B1:DY1”. Note that they are in different sheets. Yes, pasting with Ctrl+V does behave as you say, however I do not want the cells in the destination row to be split; so instead, I paste with ‘Paste Only → Text’. The expected result is that the content of $Original.B1 is pasted into $Dest.B1; $Original.C1 into $Dest.F1; and so on. Instead, $Original.C1 goes into $Dest.C1, which is hidden.
At this point, I know that copy+paste does not automatically recognize that cells are merged and treat them as a single cell. What I don’t know is whether there is even an option to make pasting behave like that. The ‘Paste Special…’ dialog doesn’t seem to offer such an option.
I wouldn’t expect any dependence on the question whether or not the source and target ranges are in different sheets.
I also wouldn’t expect the described bahaviour of PasteSpecial to skip “covered” cells. Even if these cells were in hidden columns you didn’t get that.
Next I wouldn’t expect an enhancement request concerning an additional option for PasteSpecial to the wanted effect to be promising.
You might get your result by formulae placed in the range containing the merged areas.
Not as an actual suggestion to do it that way, but as an explanation to my above comments, I provide a little example.
The first solution is based on standard functions of Calc, the second one uses “advanced” custom functions from my toolbox and can only work if the execution of document macros is permitted.
Not as an actual suggestion to do it that way, but as an explanation to my above comments, I provide a little example.
The first solution is based on standard functions of Calc, the second one uses “advanced” custom functions from my toolbox and can only work if the execution of document macros is permitted.
Hello @Voluminous,
To copy your cellrange directly to your destination range on another sheet, while considering merged cells, you could use the method Calc_copyMerged( ) as follows:
( To use these methods, please copy-paste the entire code section below to your Basic Macro Library ):
Sub copyMerged()
Calc_copyMerged( "Sheet1.B1:AG1", "Sheet2.B1:DY1" ) REM your ranges here...
End Sub
Sub Calc_copyMerged( strSourceRange As String, strTargetRange As String )
REM Copy cells from <strSourceRange> to <strTargetRange>, while treating merged cell areas as 1 cell each ( i.e., the topleft cell of the merged area ).
REM The specified ranges do not have to be of the same form or size.
REM <strSourceRange> : String identifying the Range whose ( merged ) cells to copy.
REM If this string does not contain a Sheet name, then the Source Range is assumed to be in the Active Sheet.
REM <strTargetRange> : String identifying the Range where the data should be copied to.
REM If this string does not contain a Sheet name, then the Target Range is assumed to be in the Active Sheet.
Dim oSourceRanges As Object : oSourceRanges = Calc_getMerged( strSourceRange )
Dim oTargetRanges As Object : oTargetRanges = Calc_getMerged( strTargetRange )
Dim oSource As Object : oSource = oSourceRanges.createEnumeration()
Dim oTarget As Object : oTarget = oTargetRanges.createEnumeration()
Dim oSourceRange As Object
Dim oTargetRange As Object
Dim oCell As Object
Do While oSource.hasMoreElements()
If Not oTarget.hasMoreElements() Then Stop
oSourceRange = oSource.nextElement()
oTargetRange = oTarget.nextElement()
oCell = oSourceRange.getCellByPosition( 0, 0 ) REM oCell is a non-merged cell, or the first cell of a merged area.
Select Case oCell.getType()
Case com.sun.star.table.CellContentType.FORMULA
oTargetRange.getCellByPosition( 0, 0 ).Formula = oCell.Formula
Case com.sun.star.table.CellContentType.VALUE
oTargetRange.getCellByPosition( 0, 0 ).Value = oCell.Value
Case Else
oTargetRange.getCellByPosition( 0, 0 ).String = oCell.String
End Select
Loop
End Sub
Function Calc_getMerged( strSourceRange As String ) As com.sun.star.sheet.SheetCellRanges
REM Returns a SheetCellRanges object containing all merged cell ranges, plus all non-merged cells as ranges, within the specified SourceRange.
REM <strSourceRange> : String identifying the Range whose ( merged ) cells to get.
REM If the specified string does not contain a Sheet name, then the Range is assumed to be in the Active Sheet.
Dim oDoc As Object : oDoc = ThisComponent
Dim oSheet As Object
Dim rParts() : rParts = Split( strSourceRange, "." )
If uBound( rParts ) > 0 Then REM Range contains Sheetname.
Dim strSheetName As String : strSheetName = rParts(0)
strSourceRange = Mid( strSourceRange, 2 + Len( strSheetName ) ) REM .. there might be another dot in strSourceRange.
If Left( strSheetName, 1 ) = "$" Then strSheetName = Mid( strSheetName, 2 ) REM getByName doesn''t want the $.
If oDoc.Sheets.hasByName( strSheetName ) Then oSheet = oDoc.Sheets.getByName( strSheetName )
End If
If IsNull( oSheet ) Then oSheet = oDoc.CurrentController.ActiveSheet
Dim oSourceRange As Object : oSourceRange = oSheet.getCellRangeByName( strSourceRange )
Dim oRanges As Object : oRanges = oDoc.createInstance( "com.sun.star.sheet.SheetCellRanges" )
Dim oCursor As Object
Dim oCell As Object
Dim iRow As Integer, iCol As Integer
For iRow = 0 To oSourceRange.Rows.getCount() - 1
For iCol = 0 To oSourceRange.Columns.getCount() - 1
oCell = oSourceRange.getCellByPosition( iCol, iRow )
oCursor = oSheet.createCursorByRange( oCell )
oCursor.collapseToMergedArea()
If oCell.getIsMerged() Then REM oCell.getIsMerged() only yields <TRUE> if oCell is the first cell of the merged area.
oRanges.addRangeAddress( oCursor.getRangeAddress(), False ) REM Change <oCursor> into <oCell> to return only the first cell of each merged cell area.
ElseIf Not oCursor.getIsMerged() Then REM oCursor.getIsMerged() yields <TRUE> for any oCell inside the merged area.
oRanges.addRangeAddress( oCell.getRangeAddress(), False )
End If
Next iCol
Next iRow
Calc_getMerged = oRanges
End Function
Hope it helps,
With Regards, lib