Locating merged cells

Discuss the spreadsheet application
Post Reply
DDiehl
Posts: 2
Joined: Mon Sep 11, 2017 8:54 pm

Locating merged cells

Post by DDiehl »

I have inadvertently merged cells somewhere in an OpenOffice 3.1.4 spreadsheet and "ranges containing merged cells can only be sorted without formats". Is there a way to locate or identify merged cells without manually scrolling through 31000 rows of data? I don't want to lose my formatting.
D Diehl
OpenOffice 3.1.4 on Windows 10
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Locating merged clls

Post by acknak »

Greetings and welcome to the community forum!

As far as I can tell, you can select everything and (menu) Format > Merge Cells. As long as there is at least one set of merged cells in the selected range, Calc will go through and un-merge any merged cells.

I wouldn't try this unless I had a backup copy of the current file that was not open at the time.
AOO4/LO5 • Linux • Fedora 23
DDiehl
Posts: 2
Joined: Mon Sep 11, 2017 8:54 pm

Re: Locating merged clls

Post by DDiehl »

Yes that will eliminate the problem but doesn't help from a quality control standpoint. 'Twould be helpful to know if I'm dealing with one careless oops or a systematic mistake but I guess if it doesn't crop up again I have my answer.
Thanks for the prompt reply
D Diehl
OpenOffice 3.1.4 on Windows 10
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Locating merged clls

Post by acknak »

Sorry, I don't know of any way to search for merged cells. I suppose a macro could do it, or you could search in the document xml for "spanned", although it's not simple to find what cell the xml is referring to, short of adding some specific text you can see/search for in Calc.
AOO4/LO5 • Linux • Fedora 23
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Locating merged clls

Post by Zizi64 »

Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Locating merged clls

Post by Villeroy »

Select merged ranges within current range selection

Code: Select all

Sub select_merged()
	basket = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
	view = ThisComponent.getCurrentController()
	sel = view.getSelection()
	on error goto exitErr
	  sh = sel.getSpreadsheet()
	on error goto 0
	ufr = sel.getUniqueCellFormatRanges()
	e = ufr.createEnumeration()
	while e.hasMoreElements()
		rgs = e.nextElement()
		rg = rgs.getByIndex(0)
		curs = sh.createCursorByRange(rg)
		if curs.IsMerged then basket.addRangeAddresses(rgs.getRangeAddresses(), False)
	wend
	view.select(basket)
exit sub
exitErr:
	Msgbox "Select a single cell range, please", 16, "macro: select_merged"
End Sub
First we create an empty basket of cell ranges we are going to collect.
oRangeObj.getUniqueFormatRanges() is the fastest way to get all variations of formatted cells with one API call.
The collection itself (ufr) consists of sub-collections. Each sub-collection (rgs) consists of ranges with identical formatting attributes.
The macro creates a cell cursor by the first range (rg) of each sub-collection (rgs) and tests if this cursor is merged. If the cursor of the first range is merged, then the whole sub-collection of ranges (rgs) is merged as well and we throw the rgs addresses into our basket of cell ranges.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
plankieee
Posts: 3
Joined: Sun Apr 01, 2012 6:27 pm

Re: Locating merged clls

Post by plankieee »

The following works for one application, removes the "formatting"

Open new Spreadsheet, same name perhaps with added string no formatting.

Click on Edit > Select All > Copy All on "spread sheet" with "Range containing merged cells can only be sorted without formats"


Move cursor to new spread sheet just opened to cell A1

Click on Edit > Paste SPECIAL

Now Click Data > Sort > etc

Hopefully this is helpful.
Open office 3.1 on windows 7
Post Reply