Subtotal

[Guest]

Is there a way that when the value in a cell changes that I can input a row
over the changed value and subtotal the previous information? If the value
in cell 1 was 020 and continued until cell 5. A new value is entered in cell
6 030. I want the row to be inserted over the 030 and subtotal the
informtaion for 020.

 

[Tom Ogilvy]

Sub AAAtester1()
Dim lastrow As Long, formRow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
formRow = lastrow + 1
For i = lastrow To 2 Step -1
If Cells(i, 1) <> Cells(i - 1, 1) Then
Cells(formRow, "B").Formula = "=SubTotal(9," & _
"B" & i & ":B" & formRow - 1 & ")"
Rows(i).Insert shift:=xlShiftDown
formRow = i
End If
Next
End Sub

Assumes you values are in column 1 (A) and the numbers to be subtotalled are
in column 2 (B)

Adjust to suit.