How to insert 1 row between every row that has an entry in excel

jwalkerack

Board Regular
Joined
Jun 19, 2013
Messages
81
Hello everyone , i am very new to this . i Was wondering if anyone can help me i have a two step problem

The first part of it is that i would like to be insert a new Row between each row on my Excel sheet that has data

so if i have had

Kilometres Time

1 10
3 30
5 50

i would like a new row between each of the rows

So i would get

Kilometres Time

1 10
New row
3 30
New Row
5 50


So that is the first part of my idea is to then be able make a marco to fill in the data so it would be as follows but if anyone could help me out with the first step that would be great . Thanks very much
Kilometres Time

1 10
2 15
3 30
4 40
5 50
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
try below code your data should starts in column A

Code:
Sub Test()
Dim i As Integer
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
 If Cells(i - 1, 1) <> "" And Cells(i, 1) <> "" Then Rows(i).Insert
Next
End Sub
 

jwalkerack

Board Regular
Joined
Jun 19, 2013
Messages
81
try below code your data should starts in column A

Code:
Sub Test()
Dim i As Integer
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
 If Cells(i - 1, 1) <> "" And Cells(i, 1) <> "" Then Rows(i).Insert
Next
End Sub

That is great thanks very much that worked so i now have a blank rows inserted between my points

Is there a code that i can write that will automatically fill them in

Like in the example above ??
 

jwalkerack

Board Regular
Joined
Jun 19, 2013
Messages
81

ADVERTISEMENT

Ok so i would like them to fill down the middle point between the two points

i have to columns

So it would be as follows

Time Distance

30 10
40 20
50 30
60 40

Time Distance

30 10
New row fill in middle point between the two point so 35 15
40 20
New row fill in middle point between the two point so 45 25
50 30
New row fill in middle point between the two point so 55 35
60 40

Is something like that possible ?? many thanks again
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
try below code your data should column A and B
Code:
Sub Test()
Dim i As Integer
Application.ScreenUpdating = False
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
 If Cells(i - 1, 1) <> "" And Cells(i, 1) <> "" Then Rows(i).Insert
 Cells(i, 1) = (Cells(i + 1, 1) - Cells(i - 1, 1)) / 2 + Cells(i - 1, 1)
 Cells(i, 2) = (Cells(i + 1, 2) - Cells(i - 1, 2)) / 2 + Cells(i - 1, 2)
Next
Application.ScreenUpdating = True
End Sub
 

jwalkerack

Board Regular
Joined
Jun 19, 2013
Messages
81

ADVERTISEMENT

try below code your data should column A and B
Code:
Sub Test()
Dim i As Integer
Application.ScreenUpdating = False
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
 If Cells(i - 1, 1) <> "" And Cells(i, 1) <> "" Then Rows(i).Insert
 Cells(i, 1) = (Cells(i + 1, 1) - Cells(i - 1, 1)) / 2 + Cells(i - 1, 1)
 Cells(i, 2) = (Cells(i + 1, 2) - Cells(i - 1, 2)) / 2 + Cells(i - 1, 2)
Next
Application.ScreenUpdating = True
End Sub


Hey man ,

i tried to do that but it does not work for some reason it give me a run time , error and then when i go into it . There has been a calculation done , but it looks like its average all the points . It is only the one's in the blank cells that i want to change
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
Hey man ,

i tried to do that but it does not work for some reason it give me a run time , error and then when i go into it . There has been a calculation done , but it looks like its average all the points . It is only the one's in the blank cells that i want to change

Hi same code working my file correct pls see below

Before Macro

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">30</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">40</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">50</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">60</td><td style="text-align: right;;">40</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">TEST</p><br /><br />

AFTER MACRO

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">30</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">35</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">40</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">45</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">50</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">55</td><td style="text-align: right;;">35</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">60</td><td style="text-align: right;;">40</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">TEST</p><br /><br />


Code:
Sub Test()
Dim i As Integer
Application.ScreenUpdating = False
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
 If Cells(i - 1, 1) <> "" And Cells(i, 1) <> "" Then Rows(i).Insert
 Cells(i, 1) = (Cells(i + 1, 1) - Cells(i - 1, 1)) / 2 + Cells(i - 1, 1)
 Cells(i, 2) = (Cells(i + 1, 2) - Cells(i - 1, 2)) / 2 + Cells(i - 1, 2)
Next
Application.ScreenUpdating = True
End Sub

if you are facing any problem then upload your sample file any sharing site and paste link here
 

hockeykicks30

New Member
Joined
Apr 27, 2014
Messages
14
Hi same code working my file correct pls see below

Before Macro

Excel 2007
AB
13010
24020
35030
46040

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
TEST



AFTER MACRO

Excel 2007
AB
13010
23515
34020
44525
55030
65535
76040

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
TEST




Code:
Sub Test()
Dim i As Integer
Application.ScreenUpdating = False
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
 If Cells(i - 1, 1) <> "" And Cells(i, 1) <> "" Then Rows(i).Insert
 Cells(i, 1) = (Cells(i + 1, 1) - Cells(i - 1, 1)) / 2 + Cells(i - 1, 1)
 Cells(i, 2) = (Cells(i + 1, 2) - Cells(i - 1, 2)) / 2 + Cells(i - 1, 2)
Next
Application.ScreenUpdating = True
End Sub

if you are facing any problem then upload your sample file any sharing site and paste link here


Where does this code get plugged in?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,523
Messages
5,636,820
Members
416,943
Latest member
kitkat22

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top