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

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.
Who is Participating?
leonstrykerConnect With a Mentor Commented:
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
SiddharthRoutConnect With a Mentor Commented:
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

royhsiaoConnect With a Mentor Commented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:

Will give you the last cell whereas


will give the next cell down in one.

have you tried CTRL [down arrow]?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.