Perform a VBA Function on a column with an unknown number of rows

I'm trying 开发者_StackOverflow中文版to create a function that various users can perform the function below on a column that will have a different number of rows for each user, but I can't figure out how to allow for the variability in the number of rows.

Function MKT(ByVal Temperatures As Variant) As Double
Dim Sum As Double
Dim TemperatureCount As Long
Dim GasConst As Double
Dim DeltaH As Double
Dim Conv As Double
Conv = 273.15
GasConst = 8.314472
DeltaH = 10 * GasConst
Sum = 0
For TemperatureCount = Temperatures.Cells.Count To 1 Step -1
        Sum = Sum + Exp(-DeltaH / (GasConst * (Temperatures(TemperatureCount) + Conv)))
        Next TemperatureCount
        MKT = (DeltaH / GasConst) / (-Log(Sum / Temperatures.Cells.Count)) - Conv
End Function

The function works if I set the cell value to =MKT(A1:A32557), but not if I do =MKT(A:A).

I think this could help, but I'm not sure how to implement it:

Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Also, is it possible to offset the values by 1 row to account for a header?

or to avoid hard-coding the row limit use

with sheet1
  lastRowColA = .Range("A" & .Rows.Count).End(xlUp).row
  Set rng = .range("A2:A" & lastRowColA)
end with

lastRowColA = Range("A65536").End(xlUp).Row
For Each cell In Range("a2:a" & lastRowColA)
'MsgBox cell
Next cell

You can create a subset of Temperatures by intersecting with the Used Range

Dim SubRange as Range
Set SubRange=Intersect(Temperatures,Temperatures.Parent.UsedRange)

or as nick and osknows have posted you can use .End(xlUp), but note that this ignores hidden cells

Function SubRange(theRange As Range) As Range
Dim LastRow As Long
LastRow = theRange.Parent.Cells(theRange.Parent.Rows.Count, theRange.Column).End(xlUp).Row
Set SubRange = theRange.Resize(LastRow - theRange.Row + 1)
End Function