Page 1 of 1
Locating merged cells
Posted: Mon Sep 11, 2017 9:14 pm
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.
Re: Locating merged clls
Posted: Mon Sep 11, 2017 10:15 pm
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.
Re: Locating merged clls
Posted: Mon Sep 11, 2017 11:09 pm
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
Re: Locating merged clls
Posted: Tue Sep 12, 2017 5:06 am
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.
Re: Locating merged clls
Posted: Tue Sep 12, 2017 7:17 am
by Zizi64
Re: Locating merged clls
Posted: Wed Sep 13, 2017 12:37 pm
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.
Re: Locating merged clls
Posted: Sun Mar 10, 2019 2:02 pm
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.