Insert blank row between each row of data

[Abox]

Method I'm using now is:
hold down ctrl key
select each row by clicking on the row number
right click, insert.
a blank row is then placed between each row.

This is very time consuming for large numbers of rows.

Any faster method?

 

[Mark W.]

Yes... Sort. See http://www.mrexcel.com/board/viewtopic.php?topic=22469&forum=2. The same approach can be used without the condition.

Book1
	A	B	C	D	E
1	1	Cat	1	Cat
2	2	Cat	0
3	3	Cat	3	Cat
4	4	Dog	2
5	5	Dog	5	Cat
6	6	Dog	4
7	7	Mouse	7	Dog
8	8	Mouse	6
9	9	Bird	9	Dog
10	10	Bird	8
11	11	Bird	11	Dog
12	12	Bird	10
13	11		13	Mouse
14	10		12
15	9		15	Mouse
16	8		14
17	7		17	Bird
18	6		16
19	5		19	Bird
20	4		18
21	3		21	Bird
22	2		20
23	1		23	Bird
24
Sheet1

This message was edited by Mark W. on 2002-09-18 14:35

 

[sen_edp]

Hello

this macro works if no blank rows already exist between the first row and the
last row, put your cell pointer on the first row,first cell and run
this code:

Sub AddRows()
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Mark W.]

Andreas, row insertions are "expensive". I think that was the concern of the OP.

 

[sen_edp]

On 2002-09-18 14:37, Mark W. wrote:
Andreas, row insertions are "expensive". I think that was the concern of the OP.

Hi Mark

you are correct, just gave an example of doing it with vba.

cheers

 

[Abox]

Mark W. - I'm not too advanced with this, so bear with me.
My example table has 10 rows and 4 columns. all cells contain the word 'test'.
The cells used are A1-D10.

Attempting to follow the instructions in the thread you linked to, I inserted a new blank row 1 and a new blank column A, so the list would begin in B2, as in your example.

I then placed the formula '=(B2<>B1)+A1' into cell A2 and filled down to row 11, corresponding with the last entry in column B.

I ended up with a column of 1's to the left of my table.

I was confused by the next step: "2. Enter the formula into the 1st empty cell below the last used cell in column A that adds 1 to the cell reference above it (e.g., =A13+1), and fill down until a resultant value of 1 is reached."

Obviously I'm doing something wrong. Can you give me a quick step-through?

 

[PaddyD]

if you can't get it working you can send me the workbook & I'll try & set it up for you. check your private messages.

Paddy

 

[anno]

http://www.mrexcel.com/board/viewtopic.php?topic=20461&forum=2

 

[Abox]

Mark W. - your answer gave me this idea:
In the first 3 cells of the first column to the right of my table, I entered 1,3, and 5. I then filled down to the end of the table, creating a column of odd numbers next to my table. Then I entered 2,4, and 6 underneath that, and filled down at least the same # of rows as my table, creating a column of even numbers in the blank rows. Then I sorted by my new column - the even numbered rows filled in between the odd numbered rows, and I had my spaces.

Thanks for the help!

 

[Abox]

anno - even better! Thanks! Although with mine, just dragging a 1 leaves me with a column of 1's, so I have to enter a 1 and a 2.