Macro Formula with dynamic ranges

[Jackel]

Hello all,

I am trying to build a quick and easy macro that inserts a custom concatenate formula, then selects down to the last row (usually 3-7 rows). I have the correct formula, but just cant get the ranges to work. Some help would be greatly appreciated if this is possible!!

ActiveCell.FormulaR1C1 = "=myconcatenate(R[7]C:R[1]C)"

[mdmackillop]

Sub Test()
Dim c As Range, d As Range
Set c = ActiveCell.Offset(1)
Set d = c.End(xlDown)
ActiveCell.Formula = "=myconcatenate(" & Range(c, d).Address(0, 0) & ")"
End Sub