# Insert blank row between each row of data

#### Abox

##### New Member
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?

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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:

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

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

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

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?

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.

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!

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.

Replies
3
Views
46
Replies
0
Views
43
Replies
1
Views
42
Replies
27
Views
831
Replies
2
Views
294

1,203,483
Messages
6,055,672
Members
444,806
Latest member
tofanexcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back