Inserting Rows Between Rows With Data

[Solon Aquila]

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!

 

[hiker95]

Solon Aquila,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum:
sensitive data scrubbed/removed/changed
what you have and what you expect to achieve

To attach screenshots, see below in my Signature block:
Post a screen shot with one of these:



Because of the size of your worksheet you may not be able to give us good screenshots of what it looks like (before and after).


If you are not able to give us screenshots, see below in my Signature block:
You can upload your workbook to Box Net
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Solon Aquila]

Thank you for the information, unfortunately I'm having a touch of trouble even with the xla file. Not a huge deal, I'm sure I can convey:

Excel 2007 is what we're using to try this.

The need is to convert (in this case) 3 lines of data into 9 lines of data interpolated between the original 3 data points.

Since I can't get the xla file to work, I'll try trying to make it look right here:
Start with: End with:

| Degrees | Distance | |Degrees| Distance |
| 131.00 | 100 |............. | 131.00 | 100 |
| 132.00 | 108 |..............| 131.25 | 102 |
| 133.00 | 104 | .............| 131.50 | 104 |
....................................| 131.75 | 106 |
....................................| 132.00 | 108 |
....................................| 132.25 | 107 |
....................................| 132.50 | 106 |
....................................| 132.75 | 105 |
....................................| 133.00 | 104 |


I hope that shows up as well online as it does in the preview.

Solon

 

[hiker95]

Solon Aquila,

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

The above statement does not follow thru with:
| Degrees | Distance | |Degrees| Distance |
| 131.00 | 100 |............. | 131.00 | 100 |
| 132.00 | 108 |..............| 131.25 | 102 |
| 133.00 | 104 | .............| 131.50 | 104 |
....................................| 131.75 | 106 |
....................................| 132.00 | 108 |
....................................| 132.25 | 107 |
....................................| 132.50 | 106 |
....................................| 132.75 | 105 |
....................................| 133.00 | 104 |


And, we can not tell what cell, row, column your data is in.

It would really help if you could create a new workbook, with two worksheets.

Sheet1 with the real raw data, titles, etc., as it really looks like.

Sheet2, manually created by you with the required results you are looking for.


To continue:

If you are not able to give us screenshots, see below in my Signature block:
You can upload your workbook to Box Net
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Solon Aquila]

The left-side columns represent what I receive: degrees of rotation in one column, distances assigned to those degrees in the other.

The right-side columns represent what I created to interpolate what the distance values would be between the original data points.

The difficulty is that I get up to 360 degrees of data that needs to be interpolated in increments of 0.5, 0.25, 0.125 degree increments and that's a LOT of copy/paste!

I'm happy to start with learning how to simply insert the desired number of rows, then, if possible, how to insert the formulas into those rows.

Excel Workbook
	A	B	C	D	E
5	Angle	Distance		Angle	Distance
6	131	12		131	12
7	132	27		131.125	14
8	133	54		131.25	16
9	134	93		131.375	18
10	135	14		131.5	20
11	136	20		131.625	21
12	137	27		131.75	23
13	138	36		131.875	25
14	139	45		132	27
15	140	57		132.125	30
16	141	67		132.25	34
17	142	81		132.375	37
18	143	96		132.5	41
19	144	112		132.625	44
20	145	129		132.75	47
21	146	145		132.875	51
22	147	167		133	54
23	148	190		133.125	59
24	149	213		133.25	64
25	150	238		133.375	69
26	151	264		133.5	74
27	152	289		133.625	78
28	153	315		133.75	83
29	154	341		133.875	88
...

Excel 2007



Thank you

 

[hiker95]

Solon Aquila,

Now we have good data. Thansk.

What is the formula in cell E7?

 

[Solon Aquila]

E7:
=IF((E14>E6),(E6+ABS(E14-E6)*0.125),(E6-ABS(E14-E6)*0.125))

The formula accounts for whether the data is upward or downward tending (one of the annoying bits), but in the end I interpolate between the two values very simply: difference between them multiplied by (in this case) 1/8 and either added or subtracted from the original value (depending on if upward or downward tending).

I'm still trying to figur out how to just insert the rows between the data. I saw a couple of posts for how, but kept failing.

Thanks for the help,

Solon

 

[hiker95]

Solon Aquila,


The formulae in column A look correct.

The formulae in column B do not seem to return values like your last example.

It is easier to insert rows from the bottom up.


Sample raw data:

Excel Workbook
	A	B
5	Angle	Distance
6	131	12
7	132	27
8	133	54
9	134	93
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sheet1

After the macro:

Excel Workbook
	A	B
5	Angle	Distance
6	131	12
7	131.125	14
8	131.25	16
9	131.375	18
10	131.5	21
11	131.625	23
12	131.75	26
13	131.875	28
14	132	27
15	132.125	30
16	132.25	34
17	132.375	37
18	132.5	41
19	132.625	43
20	132.75	46
21	132.875	47
22	133	54
23	133.125	59
24	133.25	62
25	133.375	63
26	133.5	63
27	133.625	62
28	133.75	60
29	133.875	58
30	134	93
31	134.125	81
32	134.25	71
33	134.375	62
34	134.5	55
35	134.625	48
36	134.75	42
37	134.875	37
38
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).

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.

Option Explicit
Sub InsertRows()
' hiker95, 03/12/2012
' http://www.mrexcel.com/forum/showthread.php?t=619771
Dim r As Long, lr As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 6 Step -1
  Rows(r + 1).Resize(7).Insert
  With Range("A" & r + 1).Resize(7)
    .FormulaR1C1 = "=R[-1]C+0.125"
  End With
  With Range("B" & r + 1).Resize(7)
    .FormulaR1C1 = "=IF((R[7]C>R[-1]C),(R[-1]C+ABS(R[7]C-R[-1]C)*0.125),(R[-1]C-ABS(R[7]C-R[-1]C)*0.125))"
    .NumberFormat = "0"
  End With
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 InsertRows macro.

 

[hiker95]

Solon Aquila,

I can change the formulae in column A to values, if you would like?

 

[Solon Aquila]

Wow... just... wow. OK, now that my mind is completely blown, it's an awful nice result. Such a small set of code and it does a ton!

The fomulae in column A are best as formulae, thank you.

The results in column B, however, don't match the results I got. I took a look and the new formulae appear a bit different from mine.
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 $.

I get the feeling this is where things get 'fun'.

Part of the 'fun' of this is that I have to interpolate the specific distance values at each degree.

Is 'resize 7' a command to tell it to insert 7 rows?
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?

Thank you for your time, and for the MrExcelHTML info, once I got it working, I kinda like it!

Solon