Locating merged cells
Locating merged cells
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
OpenOffice 3.1.4 on Windows 10
Re: Locating merged clls
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.
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
Re: Locating merged clls
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
Thanks for the prompt reply
D Diehl
OpenOffice 3.1.4 on Windows 10
OpenOffice 3.1.4 on Windows 10
Re: Locating merged clls
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
Re: Locating merged clls
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.
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.
Re: Locating merged clls
Select merged ranges within current range selection
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.
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Locating merged clls
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 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