VBA to Sum data above Header row

[kstrain]

I am trying to do a sum in cell H1, which is above my header row (row 2). I want it to sum everything in column H to the last row (which could change each time the report is ran). I need it to exclude the header in cell H2.

Any help appreciated.

 

[Flaiban]

Do it your function...
Follow example print below .

Public Function kStrain(pRng As Range) As Double

Dim xColIndex As Integer
Dim xRowIndex As Integer
Dim ws As Worksheet

Set pRng = pRng.Columns(1)
Set ws = pRng.Parent

xIndex = pRng.Column
    xRowIndex = ws.Cells(Rows.Count, xIndex).End(xlUp).Row

Set pRng = ws.Range(ws.Cells(3, xIndex), ws.Cells(xRowIndex, xIndex))
    kStrain = Application.WorksheetFunction.Sum(pRng)

End Function