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

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 $?

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

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

--
Regards,
Tom Ogilvy


"Aaron" wrote:

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 $?

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

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.


On Jul 7, 5:32 pm, Tom Ogilvy
wrote:
ActiveCell.Formula = "=SUBTOTAL(9," & rng.Address(0,0,xlA1,False) & ")"

--
Regards,
Tom Ogilvy



"Aaron" wrote:
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 $?- Hide quoted text -

- Show quoted text -