Here is one way I tend to use for this kind of problem:
Based on your example, the solution would be:
How this works:
The interesting part is:
INDIRECT is returning a reference based on the given string.
Concatenate("A",...) is creating a cell reference string, with "A" as the column, and the row number given by the next part.
MATCH(999999999,A:A,1) returns a position in the array
A:A (i.e. the whole of column A), where the value is less than (the
1 at the end specifies the search must be "less than") the number
999999999. Because this number is SO big (and you can make it even bigger or the max possible, depending on your requirements and your data set), the MATCH will search to the end of the array (A:A in this case), and return the position of the last value smaller than the big number, which will always be the last row in the column (because the 999999999 is SO big).