Insert row and add subtotal formula

[Magriza]

Hi All,

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

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?

 

[Jonmo1]

Have you tried the Subtotal Function from the Data Menu?

It can do all of what you are asking

 

[Magriza]

Ah.... thank you! Exactly what I'm after....