[Solved] Adding blank rows between rows

Discuss the spreadsheet application
Post Reply
Lindy
Posts: 2
Joined: Tue Jan 18, 2011 5:02 pm

[Solved] Adding blank rows between rows

Post by Lindy »

I'm starting out with a 100-row spreadsheet, each row is text and unique. What is the fastest way to insert 3 blank rows between each text row? I currently select three rows, right click, and click Insert Rows, which is very time consuming. Thank you.
Last edited by Lindy on Wed Jan 19, 2011 1:34 pm, edited 1 time in total.
OpenOffice 3.1 on Windows XP
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Adding blank rows between rows

Post by MrProgrammer »

In a new column immediately to the right of your data, type 0 in the first cell and fill that down (use fill handle as described in Help) to create 0, 1, 2, …, 99. In the next row (that is 101) type 0.1 and fill that down to get 0.1, 1.1, 2.1, …, 99.1. Repeat to get 0.2, 1.2, 2.2, …, 99.2 and 0.3, 1.3, 2.3, …, 99.3 in the next 200 rows. Sort the 400 rows by the new column, then delete it.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Lindy
Posts: 2
Joined: Tue Jan 18, 2011 5:02 pm

Re: [Solved] Adding blank rows between rows

Post by Lindy »

Pure genius, works great. Thanks a lot!
OpenOffice 3.1 on Windows XP
silvanet
Posts: 9
Joined: Thu Dec 09, 2010 10:46 pm

Re: [Solved] Adding blank rows between rows

Post by silvanet »

This is not a solution! Doesn't anyone in this forum know how to program in VBA? How about somebody helping with a simple VBA routine that allows this to be done without all that incredible BS extra work?
OpenOffice 3.2 on Windows 7
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Adding blank rows between rows

Post by MrProgrammer »

silvanet wrote:This is not a solution!
The OP marked this as [Solved], hence it was a solution to that problem, and Lindy's response suggests that it was a good solution. Your problem is different because you want to use VBA instead of the standard user interface, and you must open a new topic. Read Don't hijack the thread of other users.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: [Solved] Adding blank rows between rows

Post by kingfisher »

silvanet, you should also ask that question on the Macros & API board. Before I realised you had posted in the wrong place I copied some old code. It may help you so here it is:

Code: Select all

'Define the weekly table for the stock being updated, insert a row and add to table.
Dim sWStr as String, oDBase2 as Object, oCells2 as Object, lWTop as Long, lWRight as Long, _
	lWFoot as Long
sWStr = Ucase( sCode ) & "wk" : oDBase2 = vDBases.getByName( sWStr )
oCells2 = oDBase2.ReferredCells
lWFoot = oCells2.RangeAddress.EndRow + 1 : oSheet1.Rows.insertByIndex( lWFoot, 1 )
Apache OpenOffice 4.1.9 on Linux
AlwaysPaws
Posts: 6
Joined: Fri Feb 12, 2016 5:16 pm

[Solved] Adding blank rows between rows

Post by AlwaysPaws »

It may be five years later, but this helped me tremendously. I have a spreadsheet of over 10,000 items and needed to insert two extra rows between each row. Thank you, MrProgrammer.
Apache OpenOffice 4.1.2
OS X Yosemite version 10.10.5 (desktop)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Adding blank rows between rows

Post by Lupp »

I have a spreadsheet of over 10,000 items and needed to insert two extra rows between each row.
I am wondering, and a bit curious now. Would you mind to tell the reasons (probably in a somehow abstract way)? From many years of using spreadsheets I cannot remeber a case of the kind.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
AlwaysPaws
Posts: 6
Joined: Fri Feb 12, 2016 5:16 pm

Re: [Solved] Adding blank rows between rows

Post by AlwaysPaws »

Hi, I am adding products that need to be in three separate categories but I only have the categories listed on one line per item. I have about 30 categories and the sheet I'm working with has the categories all mixed up because the manufacturer uses a different categorization than I want to use on my site.

In other words, using a made-up example:
Here are three categories:

Candy Lovers candy/chocolate candy
Candy Lovers candy
Chocolate candy
Apache OpenOffice 4.1.2
OS X Yosemite version 10.10.5 (desktop)
acheide
Posts: 1
Joined: Mon Feb 18, 2019 12:18 am

Re: [Solved] Adding blank rows between rows

Post by acheide »

I used your method to double space a list by using odd numbers and then even numbers.
Thank you MrProgrammer.
OpenOffice 4.1.6 on Windows 8.1
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] Adding blank rows between rows

Post by John_Ha »

acheide wrote:I used your method to double space a list by using odd numbers and then even numbers.
Be very cautious doing so! If you create the odd and even numbers by "cell below = cell above + 2" you are referencing the very cells you will be sorting on . While the sort does work by happenstance in this case the numbers themselves change.

See sort.ods. The numbers start as 1, 2, 3, .., 18, 19, 20 but end as 1, 2, 4, 6, 8, 10, 12, .., 34, 36, 38 after the sort on Column D.
Clipboard01.png
Clipboard01.png (5.78 KiB) Viewed 5423 times
Attachments
sort.ods
(9.53 KiB) Downloaded 233 times
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Post Reply