Find & Return The Last Used Cell On An Excel Worksheet Or Column

< Back to Search results

 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
            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" '''''''''''''''''''''''''''''''''''''''

    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
        With Sheets(lWsIndex)
            Set rLastCell = .Cells(.Rows.Count, strColumn).End(xlUp)
        End With
    End If
End Function


See also:

Hide Pivot Table Fields Pivot Items by Criteria
Excel: Get Underlying Hyperlink Address
Excel VBA: Create a List of Hyperlinks
Excel VBA: Gather User Data/Input via an InputBox
Inputbox in Excel VBA
Is Workbook Open/Workbook Exists/Worksheet Exists/Auto Filter/How Many Pages Printed
Excel VBA: Create Worksheets for Each Item in an Excel Table of Data
Return Last Chosen Day of Given Month


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.


stars (0 Reviews)