Insert row above subtotal
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. |
Insert row above subtotal
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" wrote: Assuming subtotals are the only formulas set rng = Columns(3).Specialcells(xlFormulas) rng(1).EntireRow.Insert -- Regards, Tom Ogilvy "Saintsman" wrote in message ... 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. |
Insert row above subtotal
use filldown
Same as if you selected the new row and did Ctrl+D -- Regards, Tom Ogilvy "Saintsman" wrote in message ... 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" wrote: Assuming subtotals are the only formulas set rng = Columns(3).Specialcells(xlFormulas) rng(1).EntireRow.Insert -- Regards, Tom Ogilvy "Saintsman" wrote in message ... 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. |
All times are GMT +1. The time now is 07:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com