Subtotalling in VBA

  • Thread starter Thread starter Christop
  • Start date Start date
C

Christop

I have a long column of numbers which occasionally has a
blank cell.
Where the blank cell appears, I want to automate the
subtotalling of these numbers.
I started with a DO statement to loop this process until
the active cell reads "END"
My If statement was stepping down on cell, check if that
cell is blank and if so do a subtotal and then loop to
continue down the column.

PROBLEM: I don't know how to write the subtotal
instructions. How do I say subtotal the numbers above the
blank cell, up until the previous subtotal.

thanks in advance for your advice

C.
 
This was used to compare a cell to the previous one, but you should be able
to see the thought process. Create two loops, one to set your start point
and one to set the end point.
I = 2
last = 2
where = 4
Do
newVal = Cells(I, where).Value
If newVal <> oldVal Then
Cells(I, 9).FormulaR1C1 = "=SUM(R[" & last - I & "]C:R[-1]C)"
oldVal = newVal
I = I + 2
last = I
End If
I = I + 1
Loop While oldVal <> ""

Paul D
 
Christop

Assuming the data is in Column A then try....

Sub test()
Dim r As Range, c As Range
Application.ScreenUpdating = False
Columns("A:A").EntireRow.ClearOutline
ActiveSheet.Outline.SummaryRow = xlBelow
Columns("A:A").Insert Shift:=xlToRight
Set r = Range(Range("B2"), _
Range("B" & Rows.Count).End(xlUp)).Offset(1, _
0).SpecialCells(xlCellTypeBlanks)
r.Font.Bold = True
For Each c In r
Range(c.Offset(-1, 0), _
c.Offset(-1, 0).End(xlUp)).EntireRow.Group
c.Offset(0, -1) = _
"=SUBTOTAL(9," & Range(c.Offset(-1, 0), _
c.Offset(-1, 0).End(xlUp)).Address & ")"
Next c
Range("A:A").Font.Bold = True
Range("A:A").Copy
Range("B1").PasteSpecial Paste:=xlPasteAll, _
SkipBlanks:=True
Application.CutCopyMode = False
Columns("A:A").Delete Shift:=xlToLeft
Columns("A:A").SpecialCells(xlCellTypeFormulas, _
23).Replace What:="$", Replacement:="", LookAt:=xlPart
Application.ScreenUpdating = True
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| I have a long column of numbers which occasionally has a
| blank cell.
| Where the blank cell appears, I want to automate the
| subtotalling of these numbers.
| I started with a DO statement to loop this process until
| the active cell reads "END"
| My If statement was stepping down on cell, check if that
| cell is blank and if so do a subtotal and then loop to
| continue down the column.
|
| PROBLEM: I don't know how to write the subtotal
| instructions. How do I say subtotal the numbers above the
| blank cell, up until the previous subtotal.
|
| thanks in advance for your advice
|
| C.
 
Back
Top