Variable Range for Sum Formula
 

One way if all those numbers are values--not formulas:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myFormula As String

Set wks = ActiveSheet

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in that range"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
Set myCell = .Cells(.Cells.Count).Offset(1, 0)
If .Cells.Count = 1 Then
myFormula = "=" & myArea.Address(0, 0)
Else
myFormula = "=sum(" & myArea.Address(0, 0) & ")"
End If
myCell.Formula = myFormula
End With
Next myArea
End With
End Sub

Paige wrote:

I have a column of numbers and want to add subtotals at certain points.
Because of the way the file is set up and how it is used, I can't use the
normal subtotal function. Here is how the data is set up:
Col A
Row 22 Header
Row 23 34
Row 24 2
Row 25 9
blank row (or rows)
Row 27 1
blank rows (or rows)
etc.

The # of blank rows varies, as does the number of rows to add. I know how
to find the cell where I want the formula to go, but cannot figure out how to
write the formula to put there. Example above - formula goes in Cell A26; it
should add starting the cell above until it hits either a blank cell or the
word 'header'. In this case the result should be =sum(A25:A23). Cell A28
should have =sum(A27:A27). Am desperate for help on this; any input would be
appreciated.

--

Dave Peterson