Insert row above subtotal

[Saintsman]

I need to insert a row above a subtotal row
[=SUBTOTAL(9,C$1:INDIRECT(CELL("address",OFFSET(C11,-1,0))))]

rows 1 - 10 contain data
row 11 = subtotal row 1-10
rows 12 - 15 contain data
row 16 = subtotal 1-15 (Grand Totals)

I need a macro that will insert a row above row 11 - which then becomes row
12 & then repeat as needed ie always insert a row above the subtotal row.

 

[Tom Ogilvy]

Assuming subtotals are the only formulas

set rng = Columns(3).Specialcells(xlFormulas)
rng(1).EntireRow.Insert

 

[Saintsman]

Tom
There are formula I'm afraid. I defined range & following inserts row above,
what I now need to do is fill the row with previous row formula

Dim rng As Range
Set rng = Range("subtotal")
rng.EntireRow.Insert
??fill row with formula from inserted row()-1 ??

Thanks for your help

Assuming subtotals are the only formulas

set rng = Columns(3).Specialcells(xlFormulas)
rng(1).EntireRow.Insert

--
Regards,
Tom Ogilvy

I need to insert a row above a subtotal row
[=SUBTOTAL(9,C$1:INDIRECT(CELL("address",OFFSET(C11,-1,0))))]

rows 1 - 10 contain data
row 11 = subtotal row 1-10
rows 12 - 15 contain data
row 16 = subtotal 1-15 (Grand Totals)

I need a macro that will insert a row above row 11 - which then becomes
row
12 & then repeat as needed ie always insert a row above the subtotal row.

 

[Tom Ogilvy]

use filldown


Same as if you selected the new row and did Ctrl+D


--
Regards,
Tom Ogilvy

Tom
There are formula I'm afraid. I defined range & following inserts row
above,
what I now need to do is fill the row with previous row formula

Dim rng As Range
Set rng = Range("subtotal")
rng.EntireRow.Insert
??fill row with formula from inserted row()-1 ??

Thanks for your help

Assuming subtotals are the only formulas

set rng = Columns(3).Specialcells(xlFormulas)
rng(1).EntireRow.Insert

--
Regards,
Tom Ogilvy

I need to insert a row above a subtotal row
[=SUBTOTAL(9,C$1:INDIRECT(CELL("address",OFFSET(C11,-1,0))))]

rows 1 - 10 contain data
row 11 = subtotal row 1-10
rows 12 - 15 contain data
row 16 = subtotal 1-15 (Grand Totals)

I need a macro that will insert a row above row 11 - which then becomes
row
12 & then repeat as needed ie always insert a row above the subtotal
row.