EDIT 2023-02-26: Replaced the zip packages with text documents and installation macros.
One of the drawbacks of list processing in spreadsheets is the risk of losing the right references when you simply append data to the empty cells below the previously used range. For instance, the sum of a column =SUM($A$1:$A$99) the will *not* expand to =SUM($A$1:$A$100) when you simply append new data in row 100. All references expand automatically after insertion of new cells in between the existing rows. The same problem occurs when you refresh sorted and/or filtered ranges. New data are affected by the filter/sort operation only if they are entered into newly inserted cells. Chart ranges, validation lists and conditional formatting are other features where expansion by insertion takes place. (or shrink by removal respectively)
One option related to this issue is Menu:Tools>Options...Calc>General, "Expand references when new columns/rows are inserted". With this option set, you get your references adjusted even when you insert cells at the end of a list (insert columns at the right side or insert rows directly below a range). This can be turned off because this behaviour can be quite annoying when designing a calculation model.
Quite often such a list includes calculated fields:
Code: Select all
Article Count Price Amount
X-08/15 20 9.99 =Count*Price
The following pair of Python macros takes care of all this. I use them with shortcuts Ctrl+Ins and Ctrl+Del.
The routine called "insertListRows" works like this:
Get the currently selected range of cells and break silently if the selection is not not a single range.
Get the width of the current range (adjacent non-empty cells around the selection) as width of the list range.
Take the height of the current selection as row count to be inserted.
Since 2008-10-17: Unmerge merged ranges
Turn on the above mentioned option "Expand references when new rows/columns are inserted".
Insert rows according to the detected width and height.
Turn off the above mentioned option if it was not set before.
Detect all formulas in the row above and copy them down into the new rows. This can not work with the topmost row of a list.
Since 2008-10-17: Remerge previously merged ranges
Select the remaining empty range(s) for editing.
The routine called "removeListRows" works like this:
Get the currenly selected range of cells and break silently if the selection is not a single range.
Get the width of the current range (adjacent non-empty cells around the selection) as width of the list range.
Take the height of the current selection as row count to be removed.
Since 2008-10-17: Unmerge merged ranges
Remove rows according to the detected width and height.
Since 2008-10-17: Remerge previously merged ranges
Select the range where the cells have been removed.
Installation
Download the attached file and extract the python script from the zip archive. Feel free to change the script's file name, but keep the .py ending.
Application wide installation for all users:
Get administrator access and copy the Python script to <install_directory>/share/Scripts/python/
Typical path in Windows: C:\Programs\OpenOffice 2.3\share\Scripts\python\
Typical path in Linux: /opt/openoffice2.3/share/Scripts/python/
Single user installation:
Due to a bug in v3.0 Python macros work application wide only (fixed in 3.0.1)
Copy the Python script to <ooo_user_directory>/user/Scripts/python/
You may need to create the python directory below Scripts.
Typical path in Windows:
C:\Documents and Settings\username\Application Data\OpenOffice.org2\user\Scripts\python\
Typical path in Linux: ~/.openoffice2/share/Scripts/python/
When you assign the two routines to shortcuts, buttons or menu entries you'll find them in Openoffice.org Macros>user or shared>InsertCalcRows>"insertListRows" and "removeListRows" ("InsertCalcRows" refers to the file "InsertCalcRows.py")
If you are interested in the source code, open the file in a decent text editor (not Windows Notepad), able to handle UNIX line feeds (ASCII 10). Python macros require this type of line feeds and file name extension *.py
You may copy "insertListRows" and "removeListRows" into your own Python modules. Just append the two symbols to your global list g_exportedScripts.
Usage
Click any cell within your list (or directly below) where you want to insert a new row and hit Ctrl+Ins (if that is the shortcut you assigned to "InsertListRows"). Include more rows in the selection to insert more rows. Same with removal of cells. Formulas will copied down into new rows from adjacent rows above.
2010-02-03: New version, slightly enhanced and streamlined
Icons
2011-09-09: User StephTech created a pair of icons for the two macros. Feel free to use them with your user-defined icons and menues. Thank you very much, StephTec.