Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Rows vs SUM() and SUBTOTAL() | Excel Discussion (Misc queries) | |||
How do I insert a subtotal? | Excel Discussion (Misc queries) | |||
Insert Subtotal button | Excel Worksheet Functions | |||
Insert a new worksheet after subtotal | Excel Worksheet Functions | |||
Sort, Subtotal, Label Subtotal, Insert row | Excel Programming |