Find formula to find last used row in a column

[fraz627]

I have a function to find the last used row of a column. It seems to work fine most of the time, however occasionally it get a value error.
here is the function, it is stored in the first module.

Public Function LastOccupiedRow(rData As Range)
LastOccupiedRow = rData.Find(What:="*", After:=rData.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function

The actual cell has =LastOccupiedRow(A:A)
It usually happens after running a Macro that disables screen updating, hiding/un-hiding rows, for next loops ect
any suggestions

 

[jasonb75]

What error are you getting? Without testing the main possibility that comes to mind is that the range being searched is empty.

 

[fraz627]

What error are you getting? Without testing the main possibility that comes to mind is that the range being searched is empty.

the formula is in a cell, I get the #Value error. it seems its done on a for next loop.

 

[jasonb75]

That shouldn't make any difference. The way that your code is written, it should give you the last occupied row in the range that is not hidden. If the visible cells are all empty then you will get the error.

You could try making it volatile but I don't think that it is going to make any difference here. If you're not already doing so then you may want to use Application.Calculate after your loop code has finished.

Public Function LastOccupiedRow(rData As Range) As Long
Application.Volatile
LastOccupiedRow = rData.Find(What:="*", After:=rData.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function

 

[Alex Blakenburg]

it should give you the last occupied row in the range that is not hidden.

Not quite. If the hidden rows are hidden using filter this is true and it will ignore the hidden rows but if they have their property set to hidden they are still included in the Find results. This is using Lookin:=xlFormulas. Using Lookin:=xlValues it will ignore hidden regardless of how they are hidden.
Since this part of the Find command is sticky and has other consequences, I would suggest making it specific in the Find code.

If you always want the last row in the column(s) not just the last row in rData, then you could try specifying the EntireColumn eg

Public Function LastOccupiedRow(rData As Range)
    LastOccupiedRow = rData.EntireColumn.Find(What:="*", After:=rData.EntireColumn.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function

 

[kvsrinivasamurthy]

Try this.

Public Function LastOccupiedRow(rData As Range)
If rData.Cells.Count > Evaluate("Countblank(" & rData.Address & ")") Then
LastOccupiedRow = rData.Find(What:="*", After:=rData.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Else
LastOccupiedRow = ""
End If
End Function