Apache OpenOffice (AOO) Bugzilla – Issue 111211
"Deleting in merged ranges not possible" Calc 3.2.12
Last modified: 2013-07-29 06:35:30 UTC
Now - I understand that this issue was submitted as far back as 2002. It has also been reported a few times since, and in at least a couple of cases the issues were closed as 'duplicate'. I then found a reference in the "What's new in 3.2" read me that this function has been fixed in version 3.2. However, the behaviour seems to remain the same in this version. I have a spreadsheet (an order form) that is populated from columns A to L. Columns A to F form 'one big column', G to L form 'another big column'. In 'big column' A to F, headings appear, which consist of cells A to D merged and cells E and F merged, within one row. (The rows that are NOT headings retain their individual identities, and are populated with various values (SKU, size, etc) ) The contents of the order form vary from week to week, and I have to go in and remove items, which mean I am removing 6 cells (a26,b26,c26,d26,e26,f26) from a row. However, when I go to delete the cell contents, and am presented with the "Shift cells up" I receive the same error message as in all other versions of OpenOffice that I have tried "Deleting in merged ranges not possible" To recap - I am deleting non merged cells from a range in the spreadsheet that has merged cells in the same columns below the cells I am deleting, that span the SAME RANGE as the cells that I am deleting (ie: the merged cells a28-d28) and I am trying to delete (a26-d26) and shift the cells below up. This works in MS Excel - the cells are deleted, and the cells below are moved up. It seems intuitive that that is the way this function should behave - it is not as if I am removing a smaller subset of cells in this virtual 'big column' - I am in essence removing one row from the 'big column'. I downloaded and tried the CalcEasy Toolbar 1.1.2 - and it is very good at removing the whole row across ALL of the cells (BOTH 'big columns' A-F and G-L at once, but it can not remove A-D, for example. I have never explored what goes in to the 'built in' macros of a spreadsheet, but I am an IT veteran, former programmer, and would be willing to assist in any way that I was capable towards resolution of this problem.
Created attachment 69153 [details] Issue 111211 example from Erik Wallace
I can see the error message, when working with your document. But I cannot produce such a document. In all new documents, which I have tested, deleting cells was possible with the desired "shift cells up". I use DEV300m77 on WinXP. Please try it with a _new_ small document. Please use the actual version of OOo. Workaround: Delete the content and then select the cells below and drag them up.
Thank you for the quick response. Upon further testing on this issue: 1. The behavior of Open Office is the SAME whether or not the document has been created in Open Office Calc or MS Excel. I made a mistake in getting rid of a merged cell at the bottom of the spreadsheet that had 8 merged cells on a row before sending in the issue. 2. Deleting cells that lie ABOVE merged cells WILL work as long as you select the same number of cells to delete as are in the LARGEST MERGED CELL below the range you are deleting. EX: you have a spreadsheet with cells A5 to D5 merged (as a column heading). You have data in cells in rows 6 to 10. A12 to D12 are also merged (as a column heading). Further down, you merge A20 to F20 (as a PAGE footer - it is 8 cells merged as opposed to 4 cells merged in the column headers). You will NOT be able to delete and 'move up' in cells A7 to D7 (as an example) - you will get the error message "Deleting in merged ranges not possible". However, if you delete and 'move up' cells A7 to F7 this WILL work - and you will not get an error message. 3. In Excel, a similar behavior exists. The difference is that when you delete and 'move up' a smaller subset of cells (a7 to d7) than the range of the largest merged cell below (a20 to f20) Excel warns you that "doing this will unmerge some already merged cells". It allows the operation to happen, but Excel unmerges cells a20 to f20. Therefore - the difference in behavior of unmerging cells a20 to f20 in Open Office, vs deleting the cells and having Excel unmerge them for you, is that Excel is doing one step (unmerging the large merged cell) for you. Also, the message information given back to the user is probably more intuitive in Excel than in Open Office.
This Issue requires more information ('needmoreinfo'), but has not been updated within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download Please report back the outcome of your testing, so this Issue may be closed or progressed as necessary - otherwise the issue may be Resolved as Invalid in the future.
Highlight cells A28:D28 - right click - Delete... - Shift cells up Calc Rev. 1505643 Win 7: "Deleting in merged ranges not possible" Excel 2007: "This operation will cause some merged cells to unmerge. Do you wish to continue? - OK -> cells are deleted.