Find last used row in a worksheet

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 is, wether there is a value somewhere in that row or not
2. You want to know the last row that contains a value in it somewhere.

Chances are that you got to this webpage because you want to know what the last row in a worksheet with a value in it is but you received an answer you did not expect to get. And now you are wondering why Excel returns the ‘wrong’ answer.

‘Last used’ in the context of interpetation number 1 simply means the cell or row that was used during your session in the workbook. It does not necessarily have to contain a value at the moment you are trying to get an answer to your question.

In case of the second interpretation you are looking for the last row that does have a value in it the moment you are trying to get an anwser to your question.

How Excel works with ranges in memory

To fully understand why Excel gives you an unexpected answer in some occassion you must know how Excel internally works with some range related functions in memory.

Whenever you enter a value into a cell Excel copies the value and (cell)location into memory. If you then delete the cell value in the cell the location remains in memory. In other words, the cell location (or pointer for the tech savvy) is not deleted from memory.

This can cause some unexpected results when you try to write an User Defined Function (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:

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

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 can use the FIND function.

Here are two exampes for a UDF.

Code

'Find the last row which contains a value somewhere in it
Public Function LastRow() As Long
    Application.Volatile
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function
 
'Find the last used row
Public Function LastRow() As Long
    Application.Volatile
   LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Function

Usage  and limitations

This solution to find the last row with a value in it has one drawback. It won’t work properly if an autofilter is used. Make sure you disable it if it is there. An easy way to do this in VBA is by using this snippet of code.

'Disable autofilter
Sub NoAutofilter()
    If ActiveSheet.AutoFilterMode = True Then
        ActiveSheet.AutoFilterMode = False
    End If
End Sub

Courtesy: vba & excel