Calc - Merge Across Tool

[miketurn]

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

[mark_t]

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 OpenOffice.org Macros Explained

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

Option Explicit

Sub MergeAcross
	
	Dim oCurSelection As Object
	Dim oSelRangeAddress As New com.sun.star.table.CellRangeAddress
	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). _
			merge(True)
	Next i
End Sub

[BinghamJC1]

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 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.
4. Type Alt-E S (Edit→Paste Special), clear all check boxes except the
   Format box (this setting will remain until changed or the program is
   exited). Click OK or press the Enter key.

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

As mentioned earlier, each method has advantages in specific situations. The Clone Formatting method (method 1) is generally quicker, especially when only one area is being merged. A modification of the Copy and Paste Special method (method 2) can produce the desired results with fewer steps overall when a block of cells in multiple non-contiguous locations, including blocks of cells on different worksheets, need to be merged in a identical manner. After the first area has been merged (either method can be used to accomplish that), select the entire merged area and copy it. Select the topmost leftmost cell of the next location where the merge needs to occur and perform the Paste Special operation. The same number of cells on the same number of rows and columns that were copied will be merged in the new location. Repeat the paste special operation in additional locations, as needed.