2

This is a followup question to this one.

The following formula can be used to find the value of the last non-empty cell in a column, in Microsoft Excel:

=LOOKUP(2,1/(A:A<>""),A:A)

My question is, how can I convert this formula to a Macro function? So that I can use it like this:

=GetLastValueInColumn('A')

Indeed, I need to convert the letter 'A' to range A:A, but don't know how.

Community
  • 1
  • 1
B Faley
  • 17,120
  • 43
  • 133
  • 223

1 Answers1

1

Try something like,

Function GetLastValueInColumn(sCOL As String)
    GetLastValueInColumn = Parent.Cells(Rows.Count, sCOL).End(xlUp).Value
End Function

The call to Parent should resolve to the worksheet that the function is called from. This function's behaviour could be expanded to address another worksheet or the ActiveSheet if it was required to be called from within VBA.