VB Function to find last cell containing data in a column

[backfromthebush]

Hi,

Is there a function I can write using VB that will find the cell reference that contains the last data within that column?
e.g. cells A1:A10 all have data (lets say numbers for example), A11 will be empty. I need a function that will return the value A10 as the last cell in this column containing this data.
Can anyone help??!

 

[mirabeau]

Do you mean something like this?

Sub lastcellinA()
MsgBox Range("A:A")(Rows.Count).End(xlUp).Address(0, 0)
End Sub

 

[backfromthebush]

Yep, almost! Is there a way to create a custom function ? What I ultimately want to do is this:-
If cells A1:A5 had the numbers 1,2,3,4 & 5 in them respectively, I want a formula that will sum A1:A5 for me (=15). But if I was to add another value and put it in cell A6 (e.g. 6), then it automatically change the formula to A1:A6.
I guess the reason I'm doing this is that I am currently using a Sumproduct formula to do some calculations, but since this formula doesnt work when there are blank cells I have to change the cell range within the formula when I add a new line. I was hoping to automate this some how!