Insert row and add subtotal formula

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
Hi All,

I'm using this code to insert a blank row where there is a change in value in column B.

Code:
Sub InsertRows()
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = Range("B2")
    While Rng.Value <> ""
        If Rng.Value <> Rng.Offset(1).Value Then
            Rng.Offset(1).EntireRow.Insert
            Set Rng = Rng.Offset(1)
            Rng.Value = Rng.Offset(-1).Value
            Rng.Font.Bold = True
'            Rng.Offset(0, 3).FormulaR1C1 = "=SUBTOTAL(9,R2C:R2C)"
            Rng.Offset(0, 5).FormulaR1C1 = "=RC[-2]-RC[-1]"
            Rng.Offset(0, 6).FormulaR1C1 = "=RC[-3]/RC[-2]-1"
            
        End If
        Set Rng = Rng.Offset(1)
    Wend
Application.ScreenUpdating = True

End Sub

What I'd like to do is create a SUBTOTAL formula automatically. The problem is that the range differs in size with each row inserted. So, in one instance I want to sum the range E2:E2, but in the next I want to sum the range E4:E10.

Please help!

Magriza

Oh, and as a ps: does anyone know how to make the inserted row bold?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Have you tried the Subtotal Function from the Data Menu?

It can do all of what you are asking
 
Upvote 0

Forum statistics

Threads
1,190,958
Messages
5,983,843
Members
439,866
Latest member
jh3268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top