Subtotal

[Guest]

Dear Mate,

I have data in colum A which has revenue on each row and a blank row after
every regular intervals but the row count is different in each row . I need
to put subtotal formuls in each blank row. For eg.

Col A
399.2
38.5
5.0
0.2
Subtotal (A1-A4)
0.3
Subtotal (A5)
0.3
Subtotal (A6)
9.2
2.2
0.8
0.3
0.1
0.1
0.1
Subtotal (A7-13)
5.2
0.5
Subtotal (A14-15)

Regards

mat

 

[Guest]

Select all the blank cells (or Select col A, then edit/goto/special, click
blanks). Once the "correct" cells are selected, simply single click the Sum
tool!
Bob Umlas
Excel MVP

 

[Bob Phillips]

Sub test()
Dim iLastRow As Long
Dim tmp
Dim i As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow + 1
If .Cells(i, "A").Value <> "" Then
tmp = tmp + .Cells(i, "A").Value
Else
.Cells(i, "A").Value = tmp
tmp = 0
End If
Next i
End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[vezerid]

Dear Mate,

I have data in colum A which has revenue on each row and a blank row after
every regular intervals but the row count is different in each row . I need
to put subtotal formuls in each blank row. For eg.

Col A
399.2
38.5
5.0
0.2
Subtotal (A1-A4)
0.3
Subtotal (A5)
0.3
Subtotal (A6)
9.2
2.2
0.8
0.3
0.1
0.1
0.1
Subtotal (A7-13)
5.2
0.5
Subtotal (A14-15)

Regards

mat

The following macro will do what you want if the sheet is active and
data is in column A:A

Sub FillSubtotals()
LastRow = Range("A65536").End(xlUp).Row
tempSum = 0
For i = 1 to LastRow
If Cells(i,"A") <> "" Then
tempSum = tempSum + Cells(i,"A")
Else
Cells(i,"A") = tempSum
tempSum = 0
End If
Next i
End Sub

HTH
Kostis Vezerides

 

[Guest]

Dear Bob,

Excellent. Job done.

Regards
Mat

Select all the blank cells (or Select col A, then edit/goto/special, click
blanks). Once the "correct" cells are selected, simply single click the Sum
tool!
Bob Umlas
Excel MVP