Find the last row peculiarity

troy_lee

Board Regular
Joined
Feb 6, 2008
Messages
169
I noticed today that if I enter information in a row below some data and then run my function to return the last row used, the function evaluates correctly. However, if I clear the data from column I am evaluating in Last Row function, the function includes that row that used to have data in it. I had to delete the row to get the function to return the true last row. What is the deal with this and how can I avoid it? Please find my code below. Thanks in advance for the help.

Code:
Public Function FindLastRow() As Long

Dim i As Long

'Searches in Column A.
i = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
FindLastRow = i

i = 0

End Function
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
i just tried it and it works fine for me when i clear the contents of the last cell in column A

NOTE: it is checking the last row in column A...not the last used row out of all your columns...
 
Upvote 0
What do you mean by you clear the data, how are you clearing it?

I just tested your function and had data in row 10 and 20.

It returned 20 and then if I selected A20 and hit the delete key on the keyboard, it listed 10 for me?

Also you may also want to try putting in the volatile command and see if that helps:

Rich (BB code):
Public Function FindLastRow() As Long

Dim i As Long
application.volatile
'Searches in Column A.
i = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
FindLastRow = i

i = 0

End Function
Hope that helps.
 
Last edited:
Upvote 0
Not sure what column you want to find the last row in.

If you want to find the last row used in a worksheet regardless of any empty cells and regardless whatever column the last used cell is in then use this code

Code:
LastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
 
Upvote 0
Hi troy,

From your description (but what you don't mention) I'm guessing that you are calling this function from a worksheet?

2 Problems -

You should pass the range you are evaluating into the function.
You should not use the Activesheet property in the UDF.


Provided you are using Excel 2002 or later I'd suggest a function like this:
Code:
Public Function GetLastRow(rngToCheck As Range) As Double
    Dim rngFound As Range
 
    Set rngFound = rngToCheck.Find(What:="*", SearchDirection:=xlPrevious, searchorder:=xlByRows)
 
    If rngFound Is Nothing Then
        GetLastRow = rngToCheck.Row
    Else
        GetLastRow = rngFound.Row
    End If
End Function

Which is called from the worksheet like this:
=GetLastRow(A:A)


There used to be a bug that meant that you couldn't use the Range.Find() method in a function called by a worksheet, but IIRC it was fixed in XL2002.

Hope that helps...
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top