How do I append a row to the end of a spreadsheet?

[end_user]

I'm using a spreadsheet to build a stacked bar chart. Currently, if I select the last row of the spreadsheet and use the "Insert Rows" command, the current row shifts down and a new blank row is inserted above it. The functionality I like about this is that the "Insert Rows" command automatically extends the data range for the chart, and also copies and updates all formulas.

However, I really need the blank row to go after the last row, not before.

So I'm really looking for an "Append Rows" command or procedure. I'm looking for the exact same functionality as "Insert Rows" (extend data range, copy and update formulas), except the blank row will go after the selected row, and form data would NOT be copied to the new row.

Does anyone know how to do this using Calc?

[Villeroy]

Tools>Options>Calc>General>"Expand references ..."
Automatic solution: [Calc, Python] Expand/shrink list ranges

[end_user]

The "expand references" option obscurely works for extending the data range and formatting down one row, but none of the formulas get copied or updated. Is there another option I need to check for the formulas?

[acknak]

Calc never inserts content (values or formulas) automatically--at least I can't think of any examples. Values and formulas you have to copy yourself.

There is a handy shortcut for copying content in a table: double-click on the "fill handle" (black blob at the lower right of the current cell).

[Villeroy]

You try to misuse a spreadsheet as database where all this is just a matter of course.
To some extent this works ("everybody" uses Excel as poor man's database) but you should definitively install my macro. Download my zip package, extract the contained .py file to your user profile, sub-directory Script, sub-directory python (might be ~/.openoffice.org/3/user/Script/python/InsertCalcRows.py)

Call Tools>Customize, tab [Keyboard]
Assign Ctrl+Insert to [My Macros] > [InsertCalcRows] > InsertListRows
Assign Ctrl+Delete to [My Macros] > [InsertCalcRows] > RemoveListRows

Now you can select any amount of cells anywhere within or directly below the list, hit Ctrl+Insert and the macro will
- activate the above mentioned option
- unmerge any merged cells
- insert as many rows as selected and as many columns as determined by the adjacent region of non-empty cells
- drag down any formulas from above
- remerge previously merged cells (which gives expanded merged cells)
- deactivate the above mentioned option if it had been deactivated before
- select the remaining blank cells for editing

[end_user]

Calc never inserts content (values or formulas) automatically--at least I can't think of any examples. Values and formulas you have to copy yourself.

acknak, the "Insert Row" function copies the formulas, and not the values, to the newly created row. My only problem is the positioning of the new row. I'd like it to be after and not before the originally selected row.

[end_user]

You try to misuse a spreadsheet as database where all this is just a matter of course.

Villeroy, I'm not really sure how you figure that. I'm doing simple data entry, and am just looking to append a line to the end of a spreadsheet and retain the formulas and metadata that are linked to the chart. I'd say that's well within the bounds of using a spreadsheet, especially since the functionality is already there in Calc, just not in a form that's as proficient as I'd like.

"Insert Row" almost works for me, but unfortunately I'm setting this spreadsheet up as a template for many users. I'm just trying to simplify the current process of using 4 steps, down to a single step. The "expand references" option gets me down to a "2-step" process, but it's a little obscure and I'm not really looking forward to the million and a half comments from other users in the office.

I have yet to read up on your script. I'm slowly getting to it.

[chuck99]

Is it possible to extend the range to one more row than the number of rows? In that way, when you click on the last (blank) row and proceed to insert a row, the new row stays within the range.