VBA : Ranges and cells: Using an UDF to find the last used row in a worksheet
Options: Save as PDF | Save attached file | Toggle line numbers
Sometimes you want to know what the last used row in a worksheet is. There are two ways to interpret this question.
1) You really want to know the last used row in the worksheet, wether there is a value somewhere in that row or there isn't
2) You want to know the last row that contains a value in it somewhere.
Excel copies cell values and location into memory the moment you use a cell. If you then delete the cell value the cell location remains in memory. In other words, the cell location is not deleted from memory.
This can cause some unexpected results when you try to write an UDF to get the last row with a value in it.
Let me explain.
Say you have a worksheet with values in the first 4 rows. Now lets add a value in cell C5.
At this moment the last used row number is 5.
Now if you delete the value in cell C5 what is the last used row? If you would use something like:
you would still get the value 5 because this function checks the memory for the last used row. And indeed, the last used row was the 5th row.
To get the last row with a value in it we will need to use another function.
||Apr 13 2013 at 11:04 AM
Copy the code below to a module and then call the function from the worksheet by typing in the following formula:
Play arond with it and notice that it will always return the last row with a value in it somewhere.
This solution has one drawback. It won't work properly if an autofilter is used. Make sure you disable it if it is there.
Public Function LastRow() As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'To disable an autofilter you could use this code
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False