Insert row above subtotal

S

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.
 
T

Tom Ogilvy

Assuming subtotals are the only formulas

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

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

Tom Ogilvy said:
Assuming subtotals are the only formulas

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

--
Regards,
Tom Ogilvy


Saintsman said:
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.
 
T

Tom Ogilvy

use filldown


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


--
Regards,
Tom Ogilvy

Saintsman said:
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

Tom Ogilvy said:
Assuming subtotals are the only formulas

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

--
Regards,
Tom Ogilvy


Saintsman said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top