Error when finding the last non-empty cell in a column

[Davy]

Hi guys!
Could do with some help. I have a function to find the
last non-empty cell in a column. But when I call the
function, I get an error - ByRef Argument type mismatch.
The function is as below:

Function Code:

Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange,
WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Value
Exit Function
End If
Next i
End Function

My aim is to find the last non -empty cell of column H.
This column has values starting from the 23rd row. So, I
call the function in the "Calculate" event of the
worksheet like this:

Worksheets("Sheet3").Range("j2").Value = LASTINCOLUMN(H)

The column H is of Sheet3. I get the ByRef argument
mismatch at LASTINCOLUMN(H)

What is wrong here? Please help me out!

Thank you!

Davy

 

[Don Guillett]

LIC=cells(65536,"A").end(xlup).row ' the "A" can be (65536,1) if desired

 

[Davy]

It doesn't work...

It gives me the row number which is 65536 and not the
value in the last filled cell of the column

Thanks!

 

[Ron de Bruin]

Use this then Davy

MsgBox Cells(Rows.Count, "A").End(xlUp).Value