Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal
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 "Mat" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal
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) "Mat" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal
On Mar 6, 7:03 pm, Mat wrote:
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal
Dear Bob,
Excellent. Job done. Regards Mat "Bob Umlas, Excel MVP" wrote: 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 "Mat" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Subtotal, then Sort by Subtotal Amounts? | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
copy subtotal value only, subtotal value can be vlookup by others | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |