Need code to replace part of a range within a formula with a defined name

[Jeff]

I have a Sub to create subtotals using an InputBox (see below). The
resulting formula is something like this =Subtotal(9,F27:F32).

I need to incorporate a Defined Name "NextUp" which refers to
=INDIRECT("R[-1]C",0) that will automatically include the row above when a
user inserts a row above the subtotals. The resulting formula would be
=Subtotal(9,F27:NextUp).

I manually replace part of the range now with NextUp but would like to add
it to my Sub.

Thanks for your help.


Sub InsertSubtotal()

On Error Resume Next
Set rng = Application.InputBox("Select the Range to subtotal", Type:=8)
If rng Is Nothing Then
MsgBox "No range selected, exiting . . . "
Exit Sub
End If
On Error GoTo 0

Selection.Formula = "=Subtotal(9," & rng.Address(False, False) & ")"

End Sub

 

[Bob Phillips]

Sub InsertSubtotal()

On Error Resume Next
Set rng = Application.InputBox("Select the Starting to subtotal", Type:=8)
If rng Is Nothing Then
MsgBox "No range selected, exiting . . . "
Exit Sub
End If
On Error GoTo 0

Selection.Formula = "=Subtotal(9," & rng(1,1).Address(False, False) &
":NextUp)"

End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Jeff]

Perfect. Thanks.