Ask Your Question

How to find merged cells in calc

asked 2016-05-31 17:46:52 +0200

Andrea Borruso gravatar image

Dear all, I have a big spreadsheet with some merged cells. Is it possible to use find tool to find all the merged cells in it ?

Thank you

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted

answered 2016-06-01 06:06:07 +0200

JohnSUN gravatar image

Dear friend, you can use something like this macro:

REM Author JohnSUN -
Sub getMergedCells  ' Create an index of merged cells '
Const INDEX_NAME = "Index Of Merged Cells"  ' Name of sheet for a table of contents and header '
Dim oSheets As Variant  ' All sheets of current spreadsheet '
Dim oSheet As Variant   ' Single sheet '
Dim i As Long           
Dim nCount As Long      ' Count of merged cells '
Dim oCellFormatRanges As Variant
Dim oEnum As Variant
Dim oCell As Variant    ' Single cell '
Dim oCursor As Variant  ' Range of mergd cells '
Dim sRes As String      
Dim aRes As Variant     ' Array of all merged cells '
    oSheets = ThisComponent.getSheets()
    aRes = Array(Array(INDEX_NAME))
    For i = 0 To oSheets.getCount()-1   ' For each sheet in current workbook '
        oSheet = oSheets.getByIndex(i)
        oCellFormatRanges = oSheet.getCellFormatRanges()
        oEnum = oCellFormatRanges.createEnumeration()
        Do While oEnum.hasMoreElements()
            oCell = oEnum.nextElement()
            If oCell.getIsMerged() Then
                nCount = UBound(aRes)+1
                ReDim Preserve aRes(nCount)
                oCursor = oSheet.createCursorByRange(oCell)
                sRes = "=HYPERLINK(""#"+oCell.AbsoluteName+""";"""+Join(Split(oCursor.AbsoluteName,"$"),"")+""")"
                aRes(nCount) = Array(sRes)
    Next i
    If LBound(aRes()) > UBound(aRes()) Then
        MsgBox("No merged cells...")    
        If oSheets.hasByName(INDEX_NAME) Then oSheets.removeByName(INDEX_NAME)
        oSheets.insertNewByName(INDEX_NAME, 0)
        oSheet = oSheets.getByIndex(0)
        oSheet.getCellRangeByPosition(0, 0, 0, nCount).setFormulaArray(aRes)
        ThisComponent.getCurrentController().select(oSheet.getCellByPosition(0,1))  ' Show new index '
End Sub
edit flag offensive delete link more



Thank you very much.

Then I think it's not possible to use the find tool.

Andrea Borruso gravatar imageAndrea Borruso ( 2016-06-01 10:29:00 +0200 )edit

Yes, m.a.riosv and you are right - it's impossible

JohnSUN gravatar imageJohnSUN ( 2016-06-01 10:54:13 +0200 )edit

answered 2020-07-07 02:27:21 +0200

athulvis gravatar image

Use =COUNTA(first cell: last cell) to count merged cells in a column. Then copy the formula for all columns. You can click and drag for that. Then take sum of all formula cells.

edit flag offensive delete link more

answered 2016-06-09 11:17:03 +0200

Lupp gravatar image

updated 2016-06-09 11:23:46 +0200

If you urgently want a way to find merged cells without relying on user code, but using the F&R tool, you may draw upon one of some possible workarounds, all requiring additional discipline when editing the sheets.
1. Append a zero-width-space character (U+200B = UNICHAR(HEX2DEC("200B"))) at the end of the text content of each merged cell. Using regular expressions you can find the merged cells then with ​$ in 'Search For'. Please note that the quoted string already contains the invisible character U+200B in front of the "$" (2 characters!).
2. Define a named cell style ("csMerged" e.g.), and apply it to all the merged cells. Using the option 'Search for Cell Styles' you again can find merged cells and get them selected at the same time.
To rework an already existing Calc document you may first apply the perfect code given by @JohnSUN.

Regarding 1: Not well fitting in case of merged cells containing formulae which return a numeric result. Such cells should only occur as a rare exception, however.

edit flag offensive delete link more


Hi @Lupp I have received a spreadsheet with thousands of cells, and probably there are only 2-5 cells that are merged. These 2-5 cells merged are errors, and I want to find them, make a visual check and than make some correction by hand.

Than I need something like this

I think that it could be useful to have something similar in calc.

Thank you

Andrea Borruso gravatar imageAndrea Borruso ( 2016-06-10 10:45:56 +0200 )edit

For the described case JohnSUNs Routine is extremely suitable.

Lupp gravatar imageLupp ( 2016-06-10 12:01:06 +0200 )edit

answered 2016-05-31 23:42:48 +0200

m.a.riosv gravatar image

updated 2016-05-31 23:44:15 +0200

I think not, but selecting a column or a row where they are, they become easier visible.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-05-31 17:46:52 +0200

Seen: 1,777 times

Last updated: Jul 07 '20