here is the code that I got to work:

Option Explicit
________________________________________________________

Sub SumVarRangeA()
Dim x As Integer
Dim y As Integer
Dim BegRng As Variant
Dim EndRngA As Variant
Dim CountRng As Variant

x = LastCell(ActiveSheet).Row
y = LastCell(ActiveSheet).Column
BegRng = "A7"
EndRngA = "A" & x
CountRng = ActiveSheet.Range(BegRng & ":" & EndRngA).count
'MsgBox CountRng
ActiveSheet.Range(EndRngA).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[" & "-" & CountRng + 1 &
"]C:R[-1]C)"
ActiveSheet.Columns("A:A").Select
Selection.NumberFormat = "$#,##0.00"
End Sub


The above subprocedure calls  this subprocedure:

Option Explicit
________________________________________________________________

Function LastCell(WS As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case  there is not any
' data  in the worksheet

On Error Resume Next

With WS

' Find the last real  row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize  a Range object  variable for
' the last populated row.

Set LastCell = WS.Cells(LastRow&, LastCol%)

End Function

===============================================================

It's nice to be able to give something back to forums. Hope this is
helpful to anybody that happens across this problem.