Inserting Rows Between Rows With Data

Solon Aquila

New Member
Joined
Sep 24, 2011
Messages
33
My apologies for this post, but apparently I can't even figure out how to apply information that's already in the forums.

I receive worksheets with two columns of data: Degrees and Distance
(How far something is away at each degree of rotation)

I need to interpolate the data down to half, quarter, and eighth degree increments.

I manually add rows between each degree the copy/paste the .125, .250, .375 etc into each cell in the Degrees column.

I manually enter the formulas between the data in the Distance column (copy/paste after the first batch).

It works fine, but increasing 360 entries to 2800 entries is a LOT of copy-paste.

I tried starting easy: select active cells in a column (macro version of Ctrl+Shift+Down) on the idea that I don't want to insert rows between ALL the rows in the spreadsheet, just however many degrees of data we've been given. I failed even selecting active rows.

Here's my 'step 1' that failed:
(Got it from http://www.mrexcel.com/forum/showpost.php?p=2864532&postcount=3).


Sub InsertRowsBetween()
Range(Range("A1"), Range("A1").End(xlDown)).Select
End Sub


Any ideas what I missed would be appreciated and, if possible, a note about what each step does: I want to learn how to create, not just copy other people's posts.

Thank you,

Solon
PS - and how do I post a link to a thread, not just a post? My brain must really be ailing today!
 
Solon Aquila,


In mine, every formula between B6 and B14 start with "If B14>B6" and include "ABS(B14-B6)". The rest follow that method for each degree setting.
In yours, every formula looks at the cell immediately before it and then the cell 7 down from it, like pasting a formula without using the $.


What would the formulae look like for each cell in range B6:B14 (please use the $ where necessary)?

B6?

B7?....
 
Upvote 0
Solon Aquila,

Is 'resize 7' a command to tell it to insert 7 rows?

Yes.


Is "For r = lr To 6 Step -1" a command to go to the bottom row with data and walk back one step at a time?

Yes. When we normally insert/delete rows we start at the bottom, and work our way up. But, it can be done the other way.
 
Upvote 0
Sorry for the delayed response; I've been internet-noncapable for a few days. :-(

I think I confused myself when writing that one about the results of column B.. I mean the results of the second set of columns.. column E. My brain looked right at that and said "Second set=column B". Sorry about that.

Solon Aquila,
What would the formulae look like for each cell in range B6:B14 (please use the $ where necessary)?

B6?

B7?....

Every distance calculation (Column E) between two whole-number degrees is based on the distance data for those whole-number degrees. So the formulae for E7-E13 look at the difference between E6 and E14. I used the Absolute Value bit to account for the data being either rising or falling.

I don't use the $ in any of them because once I worked out the formulae for one set of degrees, I just copy/pasted through the rest of them. The super-annoying bit was dealing with expanding each degree (Column A) into 8ths and then getting the expanded column D to refer to those. Your script fixed that!

I've pasted the info for 131° through 132° with the formulae.
Excel Workbook
DE
613112.000
7131.12513.875
8131.2515.750
9131.37517.625
10131.519.500
11131.62521.375
12131.7523.250
13131.87525.125
1413227.000
...
Excel 2007
Cell Formulas
RangeFormula
E6=B6
E7=IF((E14>E6),(E6+ABS(E14-E6)*0.125),(E6-ABS(E14-E6)*0.125))
E8=IF((E14>E6),(E6+ABS(E14-E6)*0.25),(E6-ABS(E14-E6)*0.25))
E9=IF((E14>E6),(E6+ABS(E14-E6)*0.375),(E6-ABS(E14-E6)*0.375))
E10=IF((E14>E6),(E6+ABS(E14-E6)*0.5),(E6-ABS(E14-E6)*0.5))
E11=IF((E14>E6),(E6+ABS(E14-E6)*0.625),(E6-ABS(E14-E6)*0.625))
E12=IF((E14>E6),(E6+ABS(E14-E6)*0.75),(E6-ABS(E14-E6)*0.75))
E13=IF((E14>E6),(E6+ABS(E14-E6)*0.875),(E6-ABS(E14-E6)*0.875))
E14=B7


It's kinda funny to see that the formulae themselves are just minor arithmetic, it's the 'check and see what the relationships are' that makes it clunky.

Solon
 
Upvote 0
Solon Aquila,


New sample raw data in columns D and E:


Excel Workbook
DE
5AngleDistance
613112
713227
8
9
10
11
12
13
14
Sheet1





After the latest macro:


Excel Workbook
DE
5AngleDistance
613112
7131.12514
8131.2516
9131.37518
10131.520
11131.62521
12131.7523
13131.87525
1413227
15
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code:
Option Explicit
Sub InsertRowsV2()
' hiker95, 03/16/2012
' http://www.mrexcel.com/forum/showthread.php?t=619771
Dim r As Long, lr As Long, n As Long, nn As Long, dn As Double
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 4).End(xlUp).Row
For r = lr - 1 To 6 Step -1
  Rows(r + 1).Resize(7).Insert
  With Range("D" & r + 1).Resize(7)
    .FormulaR1C1 = "=R[-1]C+0.125"
  End With
  dn = 0.125: nn = 1
  For n = r + 1 To r + 7 Step 1
    With Range("E" & n)
      .FormulaR1C1 = "=IF((R14C5>R6C5),(R6C5+ABS(R14C5-R6C5)*" & dn * nn & "),(R6C5-ABS(R14C5-R6C5)*" & dn * nn & "))"
      .NumberFormat = "0"
    End With
    nn = nn + 1
  Next n
Next r
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the InsertRowsV2 macro.
 
Upvote 0
Solon Aquila,


One slight change to the latest macro - new results:


Excel Workbook
DE
5AngleDistance
613112
7131.12513.875
8131.2515.750
9131.37517.625
10131.519.500
11131.62521.375
12131.7523.250
13131.87525.125
1413227
15
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code:
Option Explicit
Sub InsertRowsV3()
' hiker95, 03/16/2012
' http://www.mrexcel.com/forum/showthread.php?t=619771
Dim r As Long, lr As Long, n As Long, nn As Long, dn As Double
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 4).End(xlUp).Row
For r = lr - 1 To 6 Step -1
  Rows(r + 1).Resize(7).Insert
  With Range("D" & r + 1).Resize(7)
    .FormulaR1C1 = "=R[-1]C+0.125"
  End With
  dn = 0.125: nn = 1
  For n = r + 1 To r + 7 Step 1
    With Range("E" & n)
      .FormulaR1C1 = "=IF((R14C5>R6C5),(R6C5+ABS(R14C5-R6C5)*" & dn * nn & "),(R6C5-ABS(R14C5-R6C5)*" & dn * nn & "))"
      .NumberFormat = "0.000"
    End With
    nn = nn + 1
  Next n
Next r
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the InsertRowsV3 macro.
 
Upvote 0
Hiker95,
Well, this is one of those 'so close' moments. I keep shaking my head trying to understand the steps of the script, and am grasping some of it.

It works fine for degrees column, always has.
The distance column works find for the first 'set' of 7-inserted rows (each compares E6 to E14, decides which of the two mathematical operations to do, and does it).
The second set of 7-inserted rows (E15 to E21) still looks at E6 as the first 'compare' cell (should be E14) and then looks at E21 as the second (should be E22).

Each set of 7-inserted rows continue to use E6 as one 'compare' cell for all the rows and the second 'compare' cell slowly drops by 1 with each 7-inserted (so that by the time we're at E71, the second 'compare' cell is E70). Actually, E70 is the highest 'compare' cell that any of the following 7-inserted rows use.

I'm over here blown away by how you got it to look at E6 across all of them... then how you got them to compare so close to the right one for the rest.

I see how dn and nn are variables that control the value in our multiplication and which row is looked at, respectively. I can't figure out how you got the 'first' compare cell to move while the second one remained E6. Nor can I figure a way to 'jump' each whole-number degree row when doing the nn+1 bit.

I think I have an idea.

What if it went to the bottom of column D and did everything as it does now to establish the degrees column, since that's working great.

Then go to the bottom again, hop to column E, hop up 1 cell (to the lowest empty inserted row) and insert the fancy formulae (that compares previous and next degree, etc) into those 7-inserted rows. Then highlight and copy those 7 rows (Shift+DownArrow 6 times, Ctrl+C), move up 8 rows and paste (UpArrow 8 times, Ctrl+V). Then move up 8 rows and paste.. and on.. up to cell E7.

There would have to be a way to identify how far up to go (perhaps "if cell location is C5R7, end steps" or some such?

I really appreciate your help on this, I knew it'd be tricky when I first posted, but I had no idea how tricky. If it becomes too great a burden for your time, we can drop the script idea. I can use a portion of the script to insert the 7 rows and handle the degrees column. I think I can find a way to manually insert the formulae, highlight and copy, then script the 'go up, paste, go up, paste' so I can enter the number of steps based on the number of degrees I have to go or something.

Solon
 
Upvote 0
Solon Aquila,


Nice catch - I should have tried with more raw data.


Sample raw data:


Excel Workbook
DE
5AngleDistance
613112
713227
813354
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sheet1





After the macro:







Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code:
Option Explicit
Sub InsertRowsV4()
' hiker95, 03/16/2012
' http://www.mrexcel.com/forum/showthread.php?t=619771
Dim r As Long, lr As Long, n As Long, nn As Long, dn As Double, sr As Long, er As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 4).End(xlUp).Row
For r = lr - 1 To 6 Step -1
  Rows(r + 1).Resize(7).Insert
  With Range("D" & r + 1).Resize(7)
    .FormulaR1C1 = "=R[-1]C+0.125"
  End With
  dn = 0.125: nn = 1
  sr = r: er = r + 8
  For n = r + 1 To r + 7 Step 1
    With Range("E" & n)
      .Formula = "=IF(($E$" & er & ">$E$" & sr & "),($E$" & sr & "+ABS($E$" & er & "-$E$" & sr & ")*" & dn * nn & "),($E$" & sr & "-ABS($E$" & er & "-$E$" & sr & ")*" & dn * nn & "))"
      .NumberFormat = "0.000"
    End With
    nn = nn + 1
  Next n
Next r
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the InsertRowsV4 macro.
 
Upvote 0
So close, part 2.

OK, this version is a LOT like the first: each cell in column E compares the cell just above and the cell 7 below (i.e. E27 compares E34 and E26). That's the trick, every cell in a group of 7 inserted rows needs to rely on the same two cells to compare and interpolate (E23-E29 should ALL rely on and interpolate E22 and E30).

I've attached a portion of a spreadsheet that has my results and formulae in A and B and your results and formulae in D and E, I hope it helps.

I may just have to sign up for that site where I can put the spreadsheet and you can view it. Do you think that would be helpful?

Excel Workbook
ABCDE
6131.0000.0012000131.0000.001200
7131.1250.0013875131.1250.0013875
8131.2500.0015750131.2500.0015938
9131.3750.0017625131.3750.0018188
10131.5000.0019500131.5000.0020625
11131.6250.0021375131.6250.0023249
12131.7500.0023250131.7500.0026059
13131.8750.0025125131.8750.0029055
14132.0000.0027000132.0000.002700
15132.1250.0030375132.1250.0030375
16132.2500.0033750132.2500.0033938
17132.3750.0037125132.3750.0037686
18132.5000.0040500132.5000.0041617
19132.6250.0043875132.6250.0045732
20132.7500.0047250132.7500.0050027
21132.8750.0050625132.8750.0054503
22133.0000.0054000133.0000.005400
23133.1250.0058875133.1250.0058875
24133.2500.0063750133.2500.0063922
25133.3750.0068625133.3750.0069141
26133.5000.0073500133.5000.0074531
27133.6250.0078375133.6250.0080095
28133.7500.0083250133.7500.0085831
29133.8750.0088125133.8750.0091741
30134.0000.0093000134.0000.009300
Sheet2
Excel 2007
Cell Formulas
RangeFormula
A7=A6+0.125
A8=A7+0.125
A9=A8+0.125
A10=A9+0.125
A11=A10+0.125
A12=A11+0.125
A13=A12+0.125
A15=A14+0.125
A16=A15+0.125
A17=A16+0.125
A18=A17+0.125
A19=A18+0.125
A20=A19+0.125
A21=A20+0.125
A23=A22+0.125
A24=A23+0.125
A25=A24+0.125
A26=A25+0.125
A27=A26+0.125
A28=A27+0.125
A29=A28+0.125
B7=IF(($B$14>$B$6),($B$6+ABS($B$14-$B$6)*0.125),($B$6-ABS($B$14-$B$6)*0.125))
B8=IF(($B$14>$B$6),($B$6+ABS($B$14-$B$6)*0.25),($B$6-ABS($B$14-$B$6)*0.25))
B9=IF(($B$14>$B$6),($B$6+ABS($B$14-$B$6)*0.375),($B$6-ABS($B$14-$B$6)*0.375))
B10=IF(($B$14>$B$6),($B$6+ABS($B$14-$B$6)*0.5),($B$6-ABS($B$14-$B$6)*0.5))
B11=IF(($B$14>$B$6),($B$6+ABS($B$14-$B$6)*0.625),($B$6-ABS($B$14-$B$6)*0.625))
B12=IF(($B$14>$B$6),($B$6+ABS($B$14-$B$6)*0.75),($B$6-ABS($B$14-$B$6)*0.75))
B13=IF(($B$14>$B$6),($B$6+ABS($B$14-$B$6)*0.875),($B$6-ABS($B$14-$B$6)*0.875))
B15=IF(($B$22>$B$14),($B$14+ABS($B$22-$B$14)*0.125),($B$14-ABS($B$22-$B$14)*0.125))
B16=IF(($B$22>$B$14),($B$14+ABS($B$22-$B$14)*0.25),($B$14-ABS($B$22-$B$14)*0.25))
B17=IF(($B$22>$B$14),($B$14+ABS($B$22-$B$14)*0.375),($B$14-ABS($B$22-$B$14)*0.375))
B18=IF(($B$22>$B$14),($B$14+ABS($B$22-$B$14)*0.5),($B$14-ABS($B$22-$B$14)*0.5))
B19=IF(($B$22>$B$14),($B$14+ABS($B$22-$B$14)*0.625),($B$14-ABS($B$22-$B$14)*0.625))
B20=IF(($B$22>$B$14),($B$14+ABS($B$22-$B$14)*0.75),($B$14-ABS($B$22-$B$14)*0.75))
B21=IF(($B$22>$B$14),($B$14+ABS($B$22-$B$14)*0.875),($B$14-ABS($B$22-$B$14)*0.875))
B23=IF(($B$30>$B$22),($B$22+ABS($B$30-$B$22)*0.125),($B$22-ABS($B$30-$B$22)*0.125))
B24=IF(($B$30>$B$22),($B$22+ABS($B$30-$B$22)*0.25),($B$22-ABS($B$30-$B$22)*0.25))
B25=IF(($B$30>$B$22),($B$22+ABS($B$30-$B$22)*0.375),($B$22-ABS($B$30-$B$22)*0.375))
B26=IF(($B$30>$B$22),($B$22+ABS($B$30-$B$22)*0.5),($B$22-ABS($B$30-$B$22)*0.5))
B27=IF(($B$30>$B$22),($B$22+ABS($B$30-$B$22)*0.625),($B$22-ABS($B$30-$B$22)*0.625))
B28=IF(($B$30>$B$22),($B$22+ABS($B$30-$B$22)*0.75),($B$22-ABS($B$30-$B$22)*0.75))
B29=IF(($B$30>$B$22),($B$22+ABS($B$30-$B$22)*0.875),($B$22-ABS($B$30-$B$22)*0.875))
D7=D6+0.125
D8=D7+0.125
D9=D8+0.125
D10=D9+0.125
D11=D10+0.125
D12=D11+0.125
D13=D12+0.125
D15=D14+0.125
D16=D15+0.125
D17=D16+0.125
D18=D17+0.125
D19=D18+0.125
D20=D19+0.125
D21=D20+0.125
D23=D22+0.125
D24=D23+0.125
D25=D24+0.125
D26=D25+0.125
D27=D26+0.125
D28=D27+0.125
D29=D28+0.125
E7=IF((E14>E6),(E6+ABS(E14-E6)*0.125),(E6-ABS(E14-E6)*0.125))
E8=IF((E15>E7),(E7+ABS(E15-E7)*0.125),(E7-ABS(E15-E7)*0.125))
E9=IF((E16>E8),(E8+ABS(E16-E8)*0.125),(E8-ABS(E16-E8)*0.125))
E10=IF((E17>E9),(E9+ABS(E17-E9)*0.125),(E9-ABS(E17-E9)*0.125))
E11=IF((E18>E10),(E10+ABS(E18-E10)*0.125),(E10-ABS(E18-E10)*0.125))
E12=IF((E19>E11),(E11+ABS(E19-E11)*0.125),(E11-ABS(E19-E11)*0.125))
E13=IF((E20>E12),(E12+ABS(E20-E12)*0.125),(E12-ABS(E20-E12)*0.125))
E15=IF((E22>E14),(E14+ABS(E22-E14)*0.125),(E14-ABS(E22-E14)*0.125))
E16=IF((E23>E15),(E15+ABS(E23-E15)*0.125),(E15-ABS(E23-E15)*0.125))
E17=IF((E24>E16),(E16+ABS(E24-E16)*0.125),(E16-ABS(E24-E16)*0.125))
E18=IF((E25>E17),(E17+ABS(E25-E17)*0.125),(E17-ABS(E25-E17)*0.125))
E19=IF((E26>E18),(E18+ABS(E26-E18)*0.125),(E18-ABS(E26-E18)*0.125))
E20=IF((E27>E19),(E19+ABS(E27-E19)*0.125),(E19-ABS(E27-E19)*0.125))
E21=IF((E28>E20),(E20+ABS(E28-E20)*0.125),(E20-ABS(E28-E20)*0.125))
E23=IF((E30>E22),(E22+ABS(E30-E22)*0.125),(E22-ABS(E30-E22)*0.125))
E24=IF((E31>E23),(E23+ABS(E31-E23)*0.125),(E23-ABS(E31-E23)*0.125))
E25=IF((E32>E24),(E24+ABS(E32-E24)*0.125),(E24-ABS(E32-E24)*0.125))
E26=IF((E33>E25),(E25+ABS(E33-E25)*0.125),(E25-ABS(E33-E25)*0.125))
E27=IF((E34>E26),(E26+ABS(E34-E26)*0.125),(E26-ABS(E34-E26)*0.125))
E28=IF((E35>E27),(E27+ABS(E35-E27)*0.125),(E27-ABS(E35-E27)*0.125))
E29=IF((E36>E28),(E28+ABS(E36-E28)*0.125),(E28-ABS(E36-E28)*0.125))
 
Upvote 0
Solon Aquila,


After the latest macro ran on columns D and E, I copied your formulae from Excel Jeanie and placed then in column B.








Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub InsertRowsV5()
' hiker95, 03/16/2012
' http://www.mrexcel.com/forum/showthread.php?t=619771
Dim r As Long, lr As Long, n As Long, nn As Long, dn As Double, sr As Long, er As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 4).End(xlUp).Row
For r = lr - 1 To 6 Step -1
  Rows(r + 1).Resize(7).Insert
  With Range("D" & r + 1).Resize(7)
    .FormulaR1C1 = "=R[-1]C+0.125"
  End With
  dn = 0.125: nn = 1
  sr = r: er = r + 8
  For n = r + 1 To r + 7 Step 1
    With Range("E" & n)
      .Formula = "=IF(($E$" & er & ">$E$" & sr & "),($E$" & sr & "+ABS($E$" & er & "-$E$" & sr & ")*" & dn * nn & "),($E$" & sr & "-ABS($E$" & er & "-$E$" & sr & ")*" & dn * nn & "))"
      .NumberFormat = "0.0000000"
    End With
    nn = nn + 1
  Next n
Next r
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the InsertRowsV5 macro.


The number format in the latest macro will display numbers in column D with 7 digits to the right of the decimal point.


It would appear that our formulae in columns B and D are the same.
 
Upvote 0
Perfect! I'm still trying to figure out what some of that stuff means, but it works perfectly.

Thank you very much for your time and expertise, this is WAY over my level of Excel knowledge.

Solon
 
Upvote 0

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
Back
Top