Excel – How to sum a Column or Range in VBA

SCENARIO

I am trying to sum column(j) that has some blank cell and place the total in the last avalaible cell in col-J.

SOLUTION

Option Explicit

Function sumRng(firstrow As Long, col As Long) As Double

Dim rng As Range
Application.Volatile

With ActiveSheet
Set rng = .Range(.Cells(firstrow, col), .Cells(.Rows.Count, col).End(xlUp))
End With

sumRng = Application.WorksheetFunction.Sum(rng)

End Function

To test this function use this Sub calling it from Macro Selection GUI or in another macro (simpy write test in another Sub) or in a cell =sumRng(1,1):

Sub test()
MsgBox sumRng(1, 10)
End Sub

Subroutine Example

Option Explicit

Sub sumRng()
    Dim rng As Range
    Dim tot As Double

    With ActiveSheet
        Set rng = .Range(.Cells(1, 10), .Cells(.Rows.Count, 10).End(xlUp))
    End With
    tot = Application.WorksheetFunction.Sum(rng)
    MsgBox tot
End Sub