Find & Return The Last Used Cell On An Excel Worksheet Or Column
Category: [Excel] Demo Available
Find & Return The Last Used Cell On An Excel Worksheet Or Column
Got any Excel/VBA Questions? Excel Help
Finding the last used cell on Worksheet, or in Column, is a vital need in Excel VBA. The special cells method includes any cells that have had ANY default formatting changed, as does the UsedRange. Here is a custom function I use often. Copy paste into a Module and Run "Sub ExampleProcedure"
Dim rLastCell As Range
Sub ExampleProcedure()
Run "LastUsedCell", 1
If Not rLastCell Is Nothing Then
MsgBox rLastCell.Address
Else
MsgBox "The Sheet is empty"
End If
End Sub
Function LastUsedCell(lWsIndex As Long, Optional strColumn As String) As Range
'''''''''''''Written by www.ozgrid.com''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Returns a Range Object of the last used cell on Worksheet or in a Column.
'Sheet index number (lWsIndex) is mandatory and Column letter (strColumn) is optional.
'Example call from Procedure in the same Module.
''''''''''''''''''''' Run "LastUsedCell", 1, "A" '''''''''''''''''''''''''''''''''''''''
'IMPORTANT. WILL FAIL IF SHEET INDEX USED IS NOT A WORKSHEET.
If strColumn = vbNullString Then
With Sheets(lWsIndex).UsedRange
Set rLastCell = .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
End With
Else
With Sheets(lWsIndex)
Set rLastCell = .Cells(.Rows.Count, strColumn).End(xlUp)
End With
End If
End Function
See also:
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.