How to find merged cells in calc

[AndreaBorruso]

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

[JohnSUN]

Dear friend, you can use something like this macro:

REM Author JohnSUN - johnsun@i.ua
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)
				oCursor.collapseToMergedArea()
				sRes = "=HYPERLINK(""#"+oCell.AbsoluteName+""";"""+Join(Split(oCursor.AbsoluteName,"$"),"")+""")"
				aRes(nCount) = Array(sRes)
			EndIf
		Loop
	Next i
	If LBound(aRes()) > UBound(aRes()) Then
		MsgBox("No merged cells...")	
	Else
		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 '
		ThisComponent.getCurrentController().select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
	EndIf
End Sub

[AndreaBorruso]

Thank you very much.

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

[JohnSUN]

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

[JohnSUN]

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

[athulvis]

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.

[Aidan]

Aha! I have a similar problem, but with about a 420k rows. And the data is fairly sparse - lots of blanks. I think “CountA(row)+CountBlank(row)” should work, but it looks as if LO has gone navel-gazing in a terminal way …

[Lupp]

Are you looking for help or for a place to post amateur poetry?
Merging cells was always doubtable and created problems if not used with extreme restraint. Violating this rule wasn’t done by libO but possibly by a navel-gazer.
A similar problem, though less easily avoided, is caused by ranges locked for array output.
You should find what you need in the attached example:
disask19109AnnoyingRanges.ods (13.8 KB)
The code is written with much less discipline than it was applied by @JohnSUN.
The report concerning merged cells will only show a name for the top-left cell of the merged range.

[mariosv]

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

[Lupp]

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.

[AndreaBorruso]

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 https://support.content.office.net/en-us/media/8dd2b96a-7e2e-4587-9f9e-7e74c2944508.jpg

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

Thank you

[Lupp]

For the described case JohnSUNs Routine is extremely suitable.