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

[jwalkerack]

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

 

[kevatarvind]

try below code your data should starts in column A

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]

try below code your data should starts in column A

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 ??

 

[kevatarvind]

yes you can but what is the criteria to fill them ?

 

[jwalkerack]

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]

try below code your data should column A and B

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]

try below code your data should column A and B

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]

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

Excel 2007
	A	B
1	30	10
2	40	20
3	50	30
4	60	40
TEST

AFTER MACRO

Excel 2007
	A	B
1	30	10
2	35	15
3	40	20
4	45	25
5	50	30
6	55	35
7	60	40
TEST

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]

Hi same code working my file correct pls see below

Before Macro

Excel 2007

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]40[/TD]

</tbody>

TEST​

AFTER MACRO

Excel 2007

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]35[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]40[/TD]

</tbody>

TEST​

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?