VBA - Insert Row based on Cell Value

[smitpau]

Hi,

I'd like to be able to create VBA which will insert a row based on a cell reference (for that row number) and will use spacing to insert the same row at say a spacing of 5.

Basically the issue is if a line is added to say a P&L (in this case Overheads), then for n number of companies each of there P&L's need a new line inserted so believe a VBA should be able to make this quicker.

Hopefully the example below is clear, I'm not sure if a Row Limit cell would have to be required so the spacing doesn't just continue to the end of the sheet which wouldn't be required.

Thanks for reading!

Book2
	A	B	C	D	E	F	G
1				VBA Inputs
2				Row	Spacing
3				9	5
4
5	P&L List		Before			After
6	Revenue		1	Company 1		1	Company 1
7	Cost of Sales		2	Revenue		2	Revenue
8	Overheads		3	Cost of Sales		3	Cost of Sales
9	Profit		4	Profit		4	Overheads
10			5			5	Profit
11			6	Company 2		6
12			7	Revenue		7	Company 2
13			8	Cost of Sales		8	Revenue
14			9	Profit		9	Cost of Sales
15						10	Overheads
16						11	Profit
Sheet1

Cell Formulas
Range	Formula
E3	E3	=C11-C6
D7:D8	D7	=A6
D9	D9	=A9
D12:D14	D12	=D7

 

[mse330]

Hi smitpau ... How about this ?

Sub InsertRows()

Dim RowStart As Integer, Spacing As Integer, lRow As Long
RowStart = [D3]
Spacing = [E3]
lRow = Range("D" & Rows.Count).End(xlUp).Row 'Change column D to the column where your data is located

For x = lRow To RowStart Step -Spacing
    Rows(x).Insert
Next

End Sub

 

[smitpau]

Thanks mse330, when I try running it the following message comes up.

 

[mse330]

Do you have other macro in Module 1 called Rows ?

 

[smitpau]

Haha sorry yes good spot, works well two things and then it should be perfect, one is that I believe for some reason it adds three to the inserted rows number input (pretty minor thing maybe able to fix) final and more important thing would it be possible to copy the row directly above to the newly insert rows below, so it keeps the flows of the formulas and formatting.

Appreciate the help.

 

[mse330]

it adds three to the inserted rows number input (pretty minor thing maybe able to fix)

So once the new row is inserted it has a value of "3" ?

Anyway, try the below which copies the above line

Sub InsertRows()

Dim RowStart As Integer, Spacing As Integer, lRow As Long
RowStart = [D3]
Spacing = [E3]
lRow = Range("D" & Rows.Count).End(xlUp).Row 'Change column D to the column where your data is located

For x = lRow To RowStart Step -Spacing
    Rows(x).Insert
    Rows(x - 1).Copy Cells(x, 1)
Next

End Sub

 

[smitpau]

Okay that's getting quite close now.

For the three bit added that was wrong sorry, what happens for some reason is that it will insert in row 10, then the spacing works well so the other row is 17 (10+6+1). If I change the row input to either 7 or 8 it will still start in row 10, not sure why.

Example below showing what happens using 8 as the row input.

Line spacer macro.xlsm
	A	B	C	D	E	F	G
1				VBA Inputs
2				Row	Spacing
3	P&L List			8	6
4	Revenue
5	Cost of Sales
6	Overheads					1	Company 1
7	Profit					2	Revenue
8						3	Cost of Sales
9						4	Overheads
10						4	Profit
11						5	Profit
12						6
13						7	Company 2
14						8	Revenue
15						9	Cost of Sales
16						10	Overheads
17						10	Profit
18						10	Profit
Sheet1

Cell Formulas
Range	Formula
E3	E3	=F13-F6
G7:G10	G7	=A4
G11	G11	=A7
G14:G16,G18	G14	=G7
G17	G17	=G11

 

[smitpau]

This seems to solve the row start bit actually, you've already done the hard part to be fair.

For x = RowStart To lRow Step Spacing

 

[smitpau]

This really is the final thing now not the end of the world if it can't be done.

For some reason the copy is like an exact copy with the same formula rather than a relative copy so would reference in the same order, so instead of two Profits as above it would reference A7 then A8.

 

[mse330]

How about the revised code below

Sub InsertRows()

Dim RowStart As Integer, Spacing As Integer, lRow As Long
RowStart = [D3]
Spacing = [E3]
lRow = Range("G" & Rows.Count).End(xlUp).Row 'Change column D to the column where your data is located

x = RowStart
Do While x <= lRow
    Rows(x).Insert
    Rows(x - 1).Copy Cells(x, 1)
    x = x + Spacing
    lRow = lRow + 1
Loop

End Sub