Sum based on row count

Hello All,

Is is possible to create a sum function on the active cell that checks the number of rows as its base?
I have tried the following but it does not work...anybody knows if it is possible to use this method?

Sub SpecialSum()


Dim rct As Long
rct = Application.WorksheetFunction.CountA(Range("B:B"))
ActiveCell.FormulaR1C1 = "=SUM(R &[Rct]C:R[-1]C)"


End Sub

:thanx:

Robert

Re: Sum based on row count

Quote from gmccreedy

something like this?

Code

Sub SpecialSum()
     
    Dim rct As Long
    rct = Application.WorksheetFunction.CountA(Range("B:B"))
    ActiveCell.FormulaR1C1 = "=SUM(R" & [rct] & "C:R" & [1] & "C)"
     
End Sub

note the " " in the formula.

HTH

Display More

Nearly..The first cell has a non numeric value and as such this macro returns
a ##### value, tired replacing the [1] by [-1] but it does not work....

Re: Sum based on row count

Never mind sussed it out.. :thanx: alot gmccreedy !!!

//Robert