Macro creates formula with $ absolute. I want to drop the $

[Aaron]

I've got this

LastRow = Cells(Rows.Count, "H").End(xlUp).Row
Set rng = Range("H2:H" & LastRow)
ActiveCell.Formula = "=SUBTOTAL(9," & rng.Address & ")"

Which gives me this on the spreadsheet:
=SUBTOTAL(9,$H$2:$H$1122)

But I want this:
=SUBTOTAL(9,H2:H1122)

How do I stop the $?

 

[Tom Ogilvy]

ActiveCell.Formula = "=SUBTOTAL(9," & rng.Address(0,0,xlA1,False) & ")"

 

[ilia]

In Excel 2003 or earlier, as soon as you start recording, the macro
recorder toolbar floats out on the screen. Next to the Stop button,
there's a toggle button for relative references - when toggled on,
macro recorder will use relative references.

In Excel 2007, this option appears in the Code group of the Developer
tab.