Subtotal like autosum with macro button; Selection as relative range in VBA

[Rowland Hamilton]

Folks,

Subtotal is superior to Sum, yadda yadda yadda. But Sum has a convenient button. When you click it, it knows to highlight the contiguous range above or to the left. I want to create a macro button for Subtotal that does the same thing, without the subtotal groupings.

How can I make the selection range read as a partial relative reference range with locked rows not columns, or at least a full relative range, so I can drag the resulting formula to the right?

I found this macro that comes close, performing the action on the selection, not the contiguous range:

Sub SUBTOTAL_Range()
    Set Rng = Selection
    Set rng1 = Rng.Offset(Rng.Rows.Count, 0).Resize(1, 1)
    rng1.Formula = "=Subtotal(9," & Rng.Address & ")"
End Sub

Besides the shortcoming of having to highlight the range (which is actually helpful when I was pulling data into my P&L template with sumif and adding subtotals beneath my ranges), my problem is that the ranges are absolute so I can't drag the formulas over.

Thank you, Rowland Hamilton

 

[Osvaldo Palmeiro]

Hi.

for row abs and column relative

rng1.Formula = "=Subtotal(9," & Rng.Address(, 0) & ")"

for both relative

rng1.Formula = "=Subtotal(9," & Rng.Address(0, 0) & ")"

 

[Rowland Hamilton]

Awesome, Osvaldo. Thank you.