Scriptorium

us fr nl




VBA : Ranges and cells: Using an UDF to find the last used row in a worksheet   us


Options: Save as PDF | Save attached file | Toggle line numbers

Details:

Type: function
Added By: Rembo
Short Description:
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.
Notes:
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:

  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 will need to use another function.
Added: Apr 13 2013 at 11:04 AM


Usage:

Copy the code below to a module and then call the function from the worksheet by typing in the following formula:

  =LastRow()

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.


Code:

Formatted | Unformatted
  1. Public Function LastRow() As Long
  2. Application.Volatile
  3. LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  4. End Function
  5.  
  6. 'To disable an autofilter you could use this code
  7. Sub NoAutofilter()
  8. If ActiveSheet.AutoFilterMode = True Then
  9. ActiveSheet.AutoFilterMode = False
  10. End If
  11. End Sub



User comments :

Add a new comment   Back to Top
Atom Feed
Contact | About This Application | Scriptorium Website