Excel macro to find and go to the last cell with text in it

Posted on 2011-05-04
Last Modified: 2012-05-11
I have an Excel 2007 spreadsheet which I share with others.  We are constantly adding text to it and it is currently up to row 305.  When I want to get to the last cell with text in it I press  CTRL + END, but it takes me to row 722 every time.  I tried deleting rows after row 305 and saving it, but CTRL + END still takes me to row 722.  Can I get a macro that will find the last row of text and then go to that row, so I can arrow down and start entering in the next row.  I plan to make the macro part of spreadsheet so that anyone who is using the spreadsheet can access the macro.  Thanks.
Question by:Chuiksgirl
    LVL 30

    Assisted Solution

    Please paste this in a module and then run it :)

    Sub Sample()
        lastrow = ActiveSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
        ActiveSheet.Cells(lastrow, 1).Select
    End Sub

    Open in new window

    LVL 29

    Accepted Solution

    Since the early days of Excel 5 I been using a variation of this function to find the last cell in a sheet:

    Public Function TrueLastCell(Optional ByVal sSheet As String, _
                        Optional ByVal sBook As String) As Range
    Dim oSheet As Worksheet
    Dim oLastRow As Range
    Dim oLastColumn As Range
        On Error GoTo ErrorHandler
        '/ set worksheet object
        sBook = IIf(sBook = "", ThisWorkbook.Name, sBook)
        sSheet = IIf(sSheet = "", ActiveSheet.Name, sSheet)
        Set oSheet = Workbooks(sBook).Worksheets(sSheet)
        '/ find the last row and last column
        Set oLastRow = oSheet.Cells.Find("*", oSheet.Cells(1048576, 16384), _
         xlFormulas, xlPart, xlByRows, xlPrevious, False)
        Set oLastColumn = oSheet.Cells.Find("*", oSheet.Cells(1048576, 16384), _
         xlFormulas, xlPart, xlByColumns, xlPrevious, False)
        '/ return the address
        Set TrueLastCell = oSheet.Cells(oLastRow.Row, oLastColumn.Column)
        GoTo Cleanup
        Exit Function
        '/ nothing found on the sheet (it is completely blank)
        Set TrueLastCell = Range("$A$1")
        Set oLastColumn = Nothing
        Set oLastRow = Nothing
        Set oSheet = Nothing
    End Function
    LVL 6

    Assisted Solution

    try this
    Sub finda()
    Dim r As Range
    Set r = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not r Is Nothing Then MsgBox "Last row is " & r.Row
    End Sub

    Open in new window

    LVL 12

    Expert Comment

    When you find the last row, you should do this as your last step.

    Application.Goto range("A" & lastCell), true


    Application.Goto range("A" & lastCell + 1), true
    LVL 59

    Assisted Solution

    by:Chris Bottomley

    Will give you the last cell whereas


    will give the next cell down in one.

    LVL 4

    Expert Comment

    have you tried CTRL [down arrow]?

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    Outlook Free & Paid Tools
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now