A selection of macros to put the formulae in place:
Code:
Sub blah()
Selection.Rows(1).Offset(-1).Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
End Sub
Code:
Sub blah2()
With Selection.Rows(1).Offset(-1)
.Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
.Value = .Value 'optional line to convert the formulae to plain values.
End With
End Sub
The first macro will put fomulae in the cells and their values will adjust immediately to what's hidden/not hidden.
In the second macro, if you remove the
.Value = .Value line the same as above,
but if you leave that line in, they'll become plain values and will
not adjust to hidden/not hidden rows, unless the macro is run again.
edit:
and another, more akin to what you originally asked:
Code:
Sub blah4()
Set visRng = Selection.Columns(1).SpecialCells(xlCellTypeVisible)
Selection.Rows(1).Offset(-1).Formula = "=sum(" & visRng.Address(0, 0) & ")"
End Sub