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

Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

Back to: Excel VBA . Got any Excel/VBA Questions? Excel Help See Also: Working With Ranges

Finding the last used cell on Worksheet, or in Column, is a vital need in Excel VBA. The SpecialCells 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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS