Ask Your Question

Calc: How to paste from multiple ordinary cells into merged cells

asked 2018-01-11 05:31:32 +0100

Voluminous gravatar image

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.

edit retag flag offensive close merge delete


I don't think there is a better way ATM. You are welcome to file an RFE at our bug tracker.

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-11 08:56:13 +0100 )edit

@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!

Lupp gravatar imageLupp ( 2018-01-11 11:11:23 +0100 )edit

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?

Lupp gravatar imageLupp ( 2018-01-11 11:20:30 +0100 )edit

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.

Voluminous gravatar imageVoluminous ( 2018-01-12 05:03:11 +0100 )edit

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.

Voluminous gravatar imageVoluminous ( 2018-01-12 05:13:27 +0100 )edit

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.

Lupp gravatar imageLupp ( 2018-01-12 13:37:26 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-01-14 07:00:37 +0100

librebel gravatar image

updated 2018-01-14 07:04:40 +0100

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()
            oTargetRange.getCellByPosition( 0, 0 ).Formula = oCell.Formula
            oTargetRange.getCellByPosition( 0, 0 ).Value = oCell.Value
        Case Else
            oTargetRange.getCellByPosition( 0, 0 ).String = oCell.String
        End Select
End Sub

Function Calc_getMerged( strSourceRange As String ) As
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( "" )
    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 ...
edit flag offensive delete link more

answered 2018-01-12 14:07:43 +0100

Lupp gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-01-11 05:31:32 +0100

Seen: 29 times

Last updated: Jan 14