Locating merged cells

Discuss the spreadsheet application

Locating merged cells

Postby DDiehl » Mon Sep 11, 2017 9:14 pm

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
DDiehl
 
Posts: 2
Joined: Mon Sep 11, 2017 8:54 pm

Re: Locating merged clls

Postby acknak » Mon Sep 11, 2017 10:15 pm

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
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Locating merged clls

Postby DDiehl » Mon Sep 11, 2017 11:09 pm

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
DDiehl
 
Posts: 2
Joined: Mon Sep 11, 2017 8:54 pm

Re: Locating merged clls

Postby acknak » Tue Sep 12, 2017 5:06 am

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
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Locating merged clls

Postby Zizi64 » Tue Sep 12, 2017 7:17 am

Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.3.2; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8474
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Locating merged clls

Postby Villeroy » Wed Sep 13, 2017 12:37 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27299
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Locating merged clls

Postby plankieee » Sun Mar 10, 2019 2:02 pm

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
plankieee
 
Posts: 3
Joined: Sun Apr 01, 2012 6:27 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests