Sub addAmtAbs()
Set myRange = Range(ActiveCell, ActiveCell.End(xlUp)) ' Substitute your range here
Dim myCount As Integer
myCount = Application.Count(myRange)
ActiveCell.Formula = "=SUM(B1:B" & myCount & ")" ' Substitute your cell address here
End Sub