Hi All,
I'm using this code to insert a blank row where there is a change in value in column B.
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?
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?