Ask Your Question

Calc - Merge Across Tool

asked 2016-08-29 17:15:20 +0100

miketurn gravatar image

Hello Everyone,

Excel contains a tool called "Merge Across" and I was wondering if anyone knows of something like this in Calc?

This tool allows you to select multiple columns and rows together (empty or containing data) and when you click on the tool it will just merge rows across, but keeps them all at one cell height rather than merging the entire selection into one big merged cell.

It is quite a helpful tool, hopefully there is something like this in Calc already created without the need for scripting. Not to say I have anything against it, but I am trying my best to not have to install Java. It looks like I am probably going to have to eventually install it because it seems that most of the tools I am looking for in my posts, have to be created.

If anyone knows of such a tool within Calc, please let me know. Or if it doesn't if anyone knows of some kind of script that does this, I would still be interested if anyone knows of one to share.

Thank You

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-08-31 01:44:14 +0100

mark_t gravatar image

updated 2016-08-31 01:45:14 +0100

I don't think there is a built in function, but can be done with a macro, example below, which could be assigned to a hot key.

I think you only need Java to record macros, not to write them in Basic or run existing macros.

It can sometimes be usefull to record a macro but they tend to be a bit crude as they use the dispatch method to perform operations that match the GUI controls. For some functions only the dispatch method is available.

Good starting point is Macros Explained

REM  *****  BASIC  *****

Option Explicit

Sub MergeAcross

    Dim oCurSelection As Object
    Dim oSelRangeAddress As New
    Dim i As Long

    oCurSelection = thisComponent.CurrentSelection
    oSelRangeAddress = oCurSelection.RangeAddress

    If oSelRangeAddress.EndColumn < oSelRangeAddress.StartColumn + 1 Then
        msgbox  "There are no columns to merge."
        Exit Sub
    End If

    For i = oSelRangeAddress.StartRow to oSelRangeAddress.EndRow
        oCurSelection.Spreadsheet. _
            getCellRangeByPosition(oSelRangeAddress.StartColumn, i, oSelRangeAddress.EndColumn, i). _
    Next i
End Sub
edit flag offensive delete link more

answered 2019-07-27 07:26:49 +0100

Bingham JC gravatar image

updated 2019-07-27 07:46:04 +0100

The absence in LibreOffice Calc of a command similar to the Microsoft Excel merge across command does not mean that Calc is entirely incapable of performing an operation similar to what the merge across command performs. The merge across command might be more descriptively be called merge cells in the rows of a selected area. A nearly identical result can be accomplished in Calc without the need of a macro. The difference being that in Excel the underlying action is a merge and the content in the leftmost cell in the series of cells being merged that has content is preserved in the merged cell. In the Calc operations that simulate this command the content of the leftmost cell in each row of cells being merged is preserved in the merged cell. Meaning that the content in all other cells is lost, (it can be restored by the undo command, but that also undoes the merge). (When the operations described below are performed in Excel (yes, they can be), the results are the same as when they are performed in Calc). The merging of cells in the rows of a selected area and conversely the merging of cells in columns of a selected area can be accomplished by two different means. (When cells containing content are merged in columns the content is handled in a similar manner to that described above, but it is the content of the topmost cells that is preserved). Both of the methods of accomplishing the merge are multiple step processes, each has some advantages over the other in specific situations. In both of these methods the first step is to merge contiguous cells in a row or column, usually this will be in the area of the spreadsheet where the merge is to occur. It does not matter if the cells that are merged are on the edge of the area to be merged, in the middle of it or not in or near it at all.

Method 1 – Clone formatting

  1. With the the merged cell selected click the Clone Formatting icon in the tool bar. (To merge cells in the rows/columns of multiple selections, double click the icon).
  2. Click the the leftmost cell in the area at either the top or the bottom row of the additional row or rows that are to be merged, for column merging click the topmost cell at either the right or left edge of the area.
  3. While holding the mouse button, drag the mouse pointer to the other edge of the area of cells, in the same row or column, that are to be merged and release the mouse button.

If multiple areas are being merged, repeat steps 2 and 3.

Method 2 – Copy and Paste Special

  1. With the the merged cell selected copy the merged cell (Ctrl-C or Edit→Copy or Alt-E Y).
  2. Click the the leftmost cell in the area at either the top or the bottom row of the additional row or rows that ...
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-08-29 17:15:20 +0100

Seen: 3,616 times

Last updated: Jul 27 '19