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

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``````

:thanx:

Robert

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Sum based on row count

something like this?

Code
``````Sub SpecialSum()

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

End Sub``````

note the " " in the formula.

HTH

• Re: Sum based on row count

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

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Sum based on row count

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

//Robert

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!