Locating merged cells

[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.

[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.

[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

[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.

[Zizi64]

For the macro solution:

https://forum.openoffice.org/en/forum/v ... 45&t=24234
https://forum.openoffice.org/en/forum/v ... =9&t=55004

[Villeroy]

Select merged ranges within current range selection

Code: Select all   Expand viewCollapse view

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.

[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.