Calc: Delete a row but retain formatting

[guyr]

I’m using the sample checkbook template to maintain my checkbook register. The template has alternating gray and white lines to make reading the entries a little easier.

https://extensions.libreoffice.org/en/extensions/show/personal-checkbook-register

Occasionally, I need to delete a row; for example, I may have accidentally entered a transaction twice, or an online payment I made and recorded never went through. I’ve been unable to find a way to delete a row but maintain the alternating gray and white coloring of rows. The workaround that I currently use is to copy all remaining rows in the sheet below the row I wish to delete, and then paste-special the copy over the row to be deleted, making sure to uncheck formatting in the paste options. Clumsy, but it works.

Is there an easier way to accomplish this? Thanks.

[Edit 5/31/22: Thanks for the replies. Responding individually below.]

[LeroyG]

@guyr

it works

Delete the row data, and sort, unmarking Include formats.

[guyr]

The approach works, assuming the spreadsheet includes a column with no missing values and a natural sort order. Fortunately for me, the checkbook spreadsheet has a natural, non-empty column suitable for sorting, the transaction date. Turns out my entries are not always in perfect date order; sometimes I’ll forget to make an entry, and have to add it (out-of-order) when my statement arrives. So, sorting will rearrange some rows; again, for this application, that is not detrimental. Just something to keep in mind when you employ this technique.

Thanks.

[Villeroy]

t78104.ods (29.3 KB) allows deletion and insertion of rows.

[guyr]

I’ve tried your solution, and it works great! (I was concerned about downloading it because of all the viruses delivered through binary attachments; to others who may find this, the attachment contains a valid spreadsheet demonstrating the solution, with no harmful code.)

I like the approach of using conditional formatting to colorize alternating rows. The original template manually colored alternate rows, which is what led to this issue. Using conditional formatting instead totally eliminates this issue, and allows the template to be much smaller.

I will modify my local template with this solution, and post it as an update to the original template. Thank you!

[JohnSUN]

Highlight the row and press backspace. Make sure the checkbox for Formats is unchecked. Press Enter

[guyr]

I may not follow this solution entirely. I followed your instructions as I understood them. The formatting of the subject row is maintained, while all data values are removed. However, this resulted in my spreadsheet having an empty row in the middle of it. And since each row in a checkbook register depends on preceding rows, all the rows following this empty row are miscalculated.

I want to actually delete the row, not just empty the cells. Am I supposed to perform another step after the one you show to completely remove the row?

[JohnSUN]

Yes, you are right, this method is suitable for clearing the contents of the last row, not in the middle of the table (I was not very careful when reading your description of the problem).
Have you ever used Format - AutoFormat Styles? (You didn’t say how you set the initial alternating row colors).
There are a lot of different design options in the list of styles, yours is similar to “Simple Grid Rows”.
Turn on the macrorecorder once, write the sequence Sheet-Delete Row then Edit - Select - Select Data Area and Format - AutoFormat Styles - Simple Grid Rows (or whatever style suits your needs or create your own style). Assign this macro to a keyboard shortcut with Tools - Customize - Keyboard tab and Context Menu tab .

[guyr]

I added a link to my original post. I obtained the checkbook register template from the LibreOffice template collection. I did not define it. The template has the alternating row colors, but no auto format styles defined. So I’m guessing the author simply applied the colors manually.

I’m not familiar with AutoFormat styles, so I looked at this Libre Office help article:

Using AutoFormat for Tables

I tried creating a new AutoFormat style from my existing sheet. However, after creating the new AutoFormat style, I immediately applied it to the same sheet I had just created it from. I tried various combinations of the checkboxes in the AutoFormat dialog box. Now matter which I unchecked, the results of applying the autoformat were not good. All the currency columns lost their currency formatting.

I’m going to attempt to use Villeroy’s idea of conditional formatting, and see if I’m more successful with that. I’ll report back. Thank for the suggestions.

[guyr]

I’ve updated the template I started with (see initial post) to use Villeroy’s suggestion of conditional formatting. It is working to my satisfaction. Thanks to everyone for the ideas.

I’d like to contribute my changes but I don’t understand how to do so. I spent about an hour reading through the Extensions area of the website, but there are no specific instructions on what to do to suggest updates to an existing template. The site does say that potential contributors should join the website mailing list. But I looked through the mailing list archives, and going back 18 months I didn’t find a single reference to a template.

Does anyone know the correct way to provide suggested updates to templates? If not, I’ll join the mailing list and see if I can accomplish something that way. Thanks.

[JohnSUN]

@guyr It seems to me that if you publish a modified template on your own behalf, indicating on the basis of which document this modification was made, then this is a completely acceptable solution - users will be able to understand that your version is newer by the date of publication. IMHO

Anyway, I don’t know how to contact Ron Faile (I hope he’s ok)