UDF for Last Cell in Range

[harrycosh]

Help!

I need a UDF which encorporates the xlUp or alternative that finds the last cell in a range. The range contains formula's and the methods that I have been using give me the result of the last cell with a formula in it rather than the last cell with text or a value in it. I have used formula's that have blanks ("") in them, which is the main thing that is confusing the issue

Thanks in advance

 

[de909]

This will give you the cell reference for the last row

Function LASTCELL(Cell As Range) As Variant
    Dim Finalrow As Long
    Finalrow = Cells(Rows.Count, Cell.Column).End(xlUp).Row
    
    LASTCELL = Cells(Finalrow, Cell.Column).Address

End Function

I hope that this helps

 

[harrycosh]

Thanks for the response de909

When I have used the function it displays the cell address of the last cell with a formula in it, which in my case is blank. Is it possible to get the address of the last cell with a text displayed in the range, rather than the last cell in the range with a formula in it?

Thanks again in advance

 

[Richard Schollar]

Perhaps this (although note that if your blanks are actually spaces " " - which is all too common, then this will fail again):

Function GetLastRow(rng As Range) As Long
On Error Resume Next
GetLastRow = rng.Find(What:="?*",After:=rng.Cells(1,1),LookIn:=xlValues,SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
End Function

 

[harrycosh]

Thanks Richard

It was exactly what I was looking for, works a treat. You are a legend!

Thanks again