Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 'There are a variety of ways to find the last row on an Excel sheet.
- 'However several methods ignore potential problems,
- 'such as inaccurate "usedrange", not including all columns, skipping filtered members, etc.
- '
- 'The below UDF tries to optimize how to grab this information.
- '#VBA #UDF #LastRow #LastColumn
- Function LastCellNumber(Optional srchRng As Range, Optional ReturnColumnInsteadOfRow As Boolean) As Long
- Dim tangoRng As Range, MakeVolatile As Boolean
- If srchRng Is Nothing Then
- Set srchRng = Sheets(Application.Caller.Worksheet.Name).UsedRange
- MakeVolatile = True
- Else
- If srchRng.Cells.Count <> Rows.Count And srchRng.Cells.Count <> Columns.Count Then
- MakeVolatile = True
- End If
- End If
- 'Sets formula to be volatile as it will not pick up changes
- If MakeVolatile Then Application.Volatile
- If ReturnColumnInsteadOfRow = False Then
- Set tangoRng = srchRng.EntireColumn.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)
- 'needed to defend against empty rows and columns
- If Not tangoRng Is Nothing Then LastCellNumber = tangoRng.Row
- Else
- Set tangoRng = srchRng.EntireRow.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious)
- 'needed to defend against empty rows and columns
- If Not tangoRng Is Nothing Then LastCellNumber = tangoRng.Column
- End If
- 'Optional output depending on if formula should return 1 instead of 0
- If tangoRng Is Nothing Then LastCellNumber = 1
- End Function
Add Comment
Please, Sign In to add comment