Insert row copies format of the row above

[Crotchety]

When I insert a row into a spreadsheet, it does so above the row containing the selected cell (afaics there is no option to change this, as there is in Writer) but it takes on the format of the line that was previously there and has now been "pushed up" to make room.

For example, I have a header row with a coloured background and bold text. If I want to insert a new data row immediately below this header, I put the cursor in a cell on the current first row below the header and press Insert Row. I end up with two rows of coloured background and bold text and have to unpick the new one.

This doesn't seem logical to me. Why wouldn't you want the new row to reflect the row you're on? Any comments/workarounds?

Cheers,
C
OO3.4.1 Windows 7-64 HP

[kingfisher]

It's annoying. IIRC a similar thing happens when you insert a column. Two methods occur to me:

1. Insert a new row below the second row (i.e the row after the header row), then cut and paste from row 2 to row 3
2. Remove all formatting from the new row. CTRL + M is supposed to do that. I sometimes copy a blank, unformatted cell and paste it to the cells from which I want to remove formatting.

I would go with the second method.

[Crotchety]

It's annoying. IIRC a similar thing happens when you insert a column. Two methods occur to me:

1. Insert a new row below the second row (i.e the row after the header row), then cut and paste from row 2 to row 3
2. Remove all formatting from the new row. CTRL + M is supposed to do that. I sometimes copy a blank, unformatted cell and paste it to the cells from which I want to remove formatting.

I would go with the second method.

I do the first and I'll try the second, thanks.

Wouldn't mind the behaviour changing, though...

[acknak]

Simplest I could come up with:
Copy a row, then paste special on the second row; choose "shift cells down"
Clear the pasted row (or clear the paste special elements you don't want)

[Crotchety]

Simplest I could come up with:
Copy a row, then paste special on the second row; choose "shift cells down"
Clear the pasted row (or clear the paste special elements you don't want)

Thanks, acknak, that's quite a neat solution.

I must say, though, that I find this whole row-handling side of things in Calc a little undeveloped and a bit buggy too. I just tried your fix and then undid it. I was left back where I was but line 2 was selected with a highlight that exceeded the row height - the height of the row above, in fact. The row itself is the right height but the row heading (containing the row number) is oversized, hence the oversized highlight - obviously a hangover from the original issue. If I then carry on clicking row headings down the list, the heading for each row expands but the highlight doesn't - nor does the row. It makes you wonder just what is going on and how it will end up when you save without noticing, although a scroll up and down gets rid of it, so I imagine it's just a refresh thing. Similar things can happen in Writer too. It's not related to zoom level, I just checked.

Another thing I find lacking is moving lines around. The last time I tried it I lost all my links. I don't remember Excel being that good at this either. I should probably look into it a bit more though and start a new thread.

[acknak]

Hmm ... I'm not seeing any highlight problem after "undo".

What flavor/version of OOo are you using? Oh, I see. It's there in your first post. (BTW, it's helpful if you add it to your forum signature: How to update your software information signature)

Maybe you can post some steps for demonstrating the problem--sometimes a sample document helps there. If it's reproducible, then we can look into reporting it.

Same idea for any glitches you run into, but it is helpful to keep them in separate threads

[Crotchety]

It was tricky to explain. I'll try and get some screenshots together when I've got a bit of time. But in the meantime, try making the line above (the one whose format gets copied) deeper than the one that's selected.

Signatures: I stopped using them for specs because when you change them they update all your posts retrospectively, so when people come to read them they're not looking at the spec that was current at the time of the problem and which may no longer result in it.

[Villeroy]

Insert a second row below the header row with the formattins you like and hide that row.
In addition you may put some formulas for calculated fields in that row and use my list keeping macro to expand your list with formulas: [Calc, Python] Expand/shrink list ranges

[Crotchety]

Devious...but I'll probably forget the row's there. Unless that's the point...

[acknak]

It was tricky to explain. I'll try and get some screenshots together when I've got a bit of time. But in the meantime, try making the line above (the one whose format gets copied) deeper than the one that's selected.

Right; I tried that, no problem.

Screenshots may not be necessary--keep it simple. Give us steps 1, 2, 3 ... include a sample document if it makes it simpler to explain.

Signatures: I stopped using them for specs because when you change them they update all your posts retrospectively, ...

Sure. It's not a really a problem, but it's your signature; there's no rule or requirement.

[Crotchety]

Right; I tried that, no problem.

You mean you saw the problem or you tried it and it worked fine?

[acknak]

Oops. I meant I still don't see the problem, even if I make the top row taller. After paste special on row two, then undo, the highlight still covers row two only.

[Villeroy]

Oops. I meant I still don't see the problem, even if I make the top row taller. After paste special on row two, then undo, the highlight still covers row two only.

Enter some column headers A B C and apply built-in style "Heading".
Enter some data below.
Imagine that you want to keep this list in geological order (latest on top), insert a new row in the second row below the heading. The newly inserted row gets the "Heading" style which I think is the problem.
My first suggestion to insert a hidden blank row may have some implications on data integrity. Unless you exclude the first two rows from the list range, the hidden row will be unhidden when using filters and It will be sorted to the bottom when you sort by any field in ascending order.
My second suggestion is (like always): Use a database. Even if you use the database data in a spreadsheet, a database makes it much easier in the long run.
Third suggestion: Keep the list from top to bottom and use my list keeping macro.

[MrProgrammer]

Copy a row, then paste special on the second row; choose "shift cells down"
Clear the pasted row (or clear the paste special elements you don't want)

I recorded a macro which copies row 2 then uses Paste Special with options Formatting, Formulas, and Shift Down. The macro recorder has limited capabilities, but it can handle those actions. You could bind this macro to a keyboard shortcut if desired (Tools > Customize).

Code: Select all

Sub Row2
Dim document as object : Dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint" : args1(0).Value = "$A$2"
dispatcher.executeDispatch(document,".uno:GoToCell","",0,args1())
dispatcher.executeDispatch(document,".uno:SelectRow","",0,Array())
dispatcher.executeDispatch(document,".uno:Copy","",0,Array())
Dim args4(5) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Flags"          : args4(0).Value = "FT"
args4(1).Name = "FormulaCommand" : args4(1).Value = 0
args4(2).Name = "SkipEmptyCells" : args4(2).Value = false
args4(3).Name = "Transpose"      : args4(3).Value = false
args4(4).Name = "AsLink"         : args4(4).Value = false
args4(5).Name = "MoveMode"       : args4(5).Value = 0
dispatcher.executeDispatch(document,".uno:InsertContents","",0,args4())
dispatcher.executeDispatch(document,".uno:GoToCell","",0,args1())
End Sub

[Crotchety]

I'm very impressed with all these workarounds but to my mind this is just trying to get round an implementation that could have been thought through better. I am not very comfortable with criticising voluntary efforts but I think it needs saying as all involved are trying to produce a great product.

It needs to change, imo.

[Crotchety]

Oops. I meant I still don't see the problem, even if I make the top row taller. After paste special on row two, then undo, the highlight still covers row two only.

Just tried this again on the original sheet and a new one and it's not doing it anymore! Oh, well, must have been a Monday... (wasn't there a full moon then?)

[RoryOF]

It is manned by voluntary efforts, as you note. If you feel something needs to change, feel free to change it and join your effort to the communal effort.

[Crotchety]

As I was just explaining to Hagar, I don't like getting involved with bugzillas but it's not fair to expect mods to keep posting on my behalf (even if it seems their word would carry more weight) so I'll bite the bullet again, I suppose...

[Crotchety]

https://issues.apache.org/ooo/show_bug.cgi?id=104110

This is from 2009, classed as trivial - normal at the very least, it's disruptive - and hasn't even been confirmed yet. I would say this thread amounts to such, so how do you get that status changed?

[RoryOF]

The priority of a non-critical bug is determined by the number of votes it acquires. There is a VOTE link at the top of the bug screen. Note that everybody's favourite bug is, in their eyes, worthy of immediate solution. The resources of programmers are mainly devoted to stability improvements and new functionality, particularly in the case where there is a reasonable work-around.

[Crotchety]

Can it at least be confirmed by someone or it'll never even get looked at?

Maybe it could be rolled into an enhancement. I think I mentioned it earlier but row handling could use a bit. There is no above/below or number of rows option as there is in Writer's table-handling, but at least that shows that similar adaptable code exists. And then there was the issue of broken links when inserting rows, which I will look into again when I have finished this batch of work I'm on.

Cheers,
C

And btw, I think voting as a way of determining policy priority has it's limits. Just take Democracy, for example...

[Villeroy]

Most people keep their lists top-down. The current behaviour copies the formatting from the last row into the new row. WIth one exception, I do not keep lists in spreadsheets anymore. Even the simpliest list deserves to be stored in a database where this problem and dozends of other problems does not exist.